[Home] [Help]
PACKAGE BODY: APPS.FA_CALC_DEPRN_BASIS1_PKG
Source
1 PACKAGE BODY FA_CALC_DEPRN_BASIS1_PKG as
2 /* $Header: faxcdb1b.pls 120.67.12010000.4 2009/02/04 14:22:51 souroy ship $ */
3 -- global variables
4 g_log_level_rec fa_api_types.log_level_rec_type;
5 g_book_type_code fa_deprn_periods.book_type_code%TYPE;
6 g_fiscal_year1 fa_fiscal_year.fiscal_year%TYPE;
7 g_fiscal_year2 fa_fiscal_year.fiscal_year%TYPE;
8 g_period_num1 fa_calendar_periods.period_num%TYPE;
9 g_period_num2 fa_calendar_periods.period_num%TYPE;
10 g_end_date1 fa_calendar_periods.end_date%TYPE;
11 g_end_date2 fa_calendar_periods.end_date%TYPE;
12 g_num_per_fy fa_calendar_types.number_per_fiscal_year%TYPE;
13
14 FUNCTION faxcdb(
15 rule_in IN fa_std_types.fa_deprn_rule_in_struct,
16 rule_out OUT NOCOPY fa_std_types.fa_deprn_rule_out_struct,
17 p_amortization_start_date IN date default null,
18 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
19 return boolean is
20
21 rule_id number(15) DEFAULT NULL;
22 l_rule_name varchar2(80) DEFAULT NULL;
23 l_rule_formula varchar2(2000) DEFAULT NULL;
24
25 -- For Event Type: DEPRECIATE_FLAG_ADJ
26 l_last_trx_count NUMBER :=0;
27 l_book_type_code VARCHAR2(15) DEFAULT NULL;
28 l_asset_id NUMBER DEFAULT NULL;
29
30 -- Added for group depreciation
31 l_period_update_flag VARCHAR2(1) DEFAULT NULL;
32 l_apply_reduction_flag VARCHAR2(1) DEFAULT NULL;
33 l_subtract_ytd_flag VARCHAR2(1) DEFAULT NULL;
34
35 -- Proceeds
36 l_ltd_proceeds NUMBER := 0;
37 l_ytd_proceeds NUMBER := 0;
38
39 -- Retired cost
40 l_retired_cost NUMBER := 0;
41 l_adj_reserve NUMBER := 0;
42
43 -- For Event type: INITIAL_ADDITION
44 l_amort_fiscal_year NUMBER :=0;
45 l_amort_period_num NUMBER :=0;
46 l_amort_period_counter NUMBER :=0;
47 l_amort_salvage_value NUMBER :=0;
48
49 -- For reduction rate
50 l_member_reduction_rate NUMBER := null;
51 l_member_transaction_flag VARCHAR2(1) := null;
52
53 l_calling_fn varchar2(35) := 'fa_calc_deprn_basis1_pkg.faxcdb';
54
55 -- Bug 6665510: FP: Japan Tax Reform Project
56 l_rate_in_use NUMBER;
57 l_old_method_code VARCHAR2(12);
58 l_old_salvage_value NUMBER;
59 l_old_cost NUMBER;
60
61 faxcdb_err exception;
62 calc_basis_err exception;
63 begin
64
65 ------------------------------------------------------------
66 -- Debug input parameters
67 ------------------------------------------------------------
68
69 -- fa_debug_pkg.initialize; -- removed to not clear the message stack.
70 if p_log_level_rec.statement_level then
71 fa_debug_pkg.add(fname=>'faxcdb',
72 element=>'rule_in.event_type',
73 value=> rule_in.event_type
74 ,p_log_level_rec => p_log_level_rec);
75 fa_debug_pkg.add(fname=>'faxcdb',
76 element=>'rule_in.asset_id',
77 value=> rule_in.asset_id
78 ,p_log_level_rec => p_log_level_rec);
79 fa_debug_pkg.add(fname=>'faxcdb',
80 element=>'rule_in.group_asset_id',
81 value=> rule_in.group_asset_id
82 ,p_log_level_rec => p_log_level_rec);
83 fa_debug_pkg.add(fname=>'faxcdb',
84 element=>'rule_in.book_type_code',
85 value=> rule_in.book_type_code
86 ,p_log_level_rec => p_log_level_rec);
87 fa_debug_pkg.add(fname=>'faxcdb',
88 element=>'rule_in.asset_type',
89 value=> rule_in.asset_type
90 ,p_log_level_rec => p_log_level_rec);
91 fa_debug_pkg.add(fname=>'faxcdb',
92 element=>'rule_in.depreciate_flag',
93 value=> rule_in.depreciate_flag
94 ,p_log_level_rec => p_log_level_rec);
95 fa_debug_pkg.add(fname=>'faxcdb',
96 element=>'rule_in.method_code',
97 value=> rule_in.method_code
98 ,p_log_level_rec => p_log_level_rec);
99 fa_debug_pkg.add(fname=>'faxcdb',
100 element=>'rule_in.life_in_months',
101 value=> rule_in.life_in_months
102 ,p_log_level_rec => p_log_level_rec);
103 fa_debug_pkg.add(fname=>'faxcdb',
104 element=>'rule_in.method_id',
105 value=> rule_in.method_id
106 ,p_log_level_rec => p_log_level_rec);
107 fa_debug_pkg.add(fname=>'faxcdb',
108 element=>'rule_in.method_type',
109 value=> rule_in.method_type
110 ,p_log_level_rec => p_log_level_rec);
111 fa_debug_pkg.add(fname=>'faxcdb',
112 element=>'rule_in.calc_basis',
113 value=> rule_in.calc_basis
114 ,p_log_level_rec => p_log_level_rec);
115 fa_debug_pkg.add(fname=>'faxcdb',
116 element=>'rule_in.adjustment_amount',
117 value=> rule_in.adjustment_amount
118 ,p_log_level_rec => p_log_level_rec);
119 fa_debug_pkg.add(fname=>'faxcdb',
120 element=>'rule_in.transaction_flag',
121 value=> rule_in.transaction_flag
122 ,p_log_level_rec => p_log_level_rec);
123 fa_debug_pkg.add(fname=>'faxcdb',
124 element=>'rule_in.cost',
125 value=> rule_in.cost
126 ,p_log_level_rec => p_log_level_rec);
127 fa_debug_pkg.add(fname=>'faxcdb',
128 element=>'rule_in.salvage_value',
129 value=> rule_in.salvage_value
130 ,p_log_level_rec => p_log_level_rec);
131 fa_debug_pkg.add(fname=>'faxcdb',
132 element=>'rule_in.recoverable_cost',
133 value=> rule_in.recoverable_cost
134 ,p_log_level_rec => p_log_level_rec);
135 fa_debug_pkg.add(fname=>'faxcdb',
136 element=>'rule_in.adjusted_cost',
137 value=> rule_in.adjusted_cost
138 ,p_log_level_rec => p_log_level_rec);
139 fa_debug_pkg.add(fname=>'faxcdb',
140 element=>'rule_in.current_total_rsv',
141 value=> rule_in.current_total_rsv
142 ,p_log_level_rec => p_log_level_rec);
143 fa_debug_pkg.add(fname=>'faxcdb',
144 element=>'rule_in.current_rsv',
145 value=> rule_in.current_rsv
146 ,p_log_level_rec => p_log_level_rec);
147 fa_debug_pkg.add(fname=>'faxcdb',
148 element=>'rule_in.current_total_ytd',
149 value=> rule_in.current_total_ytd
150 ,p_log_level_rec => p_log_level_rec);
151 fa_debug_pkg.add(fname=>'faxcdb',
152 element=>'rule_in.current_ytd',
153 value=> rule_in.current_ytd
154 ,p_log_level_rec => p_log_level_rec);
155 fa_debug_pkg.add(fname=>'faxcdb',
156 element=>'rule_in.hyp_basis',
157 value=> rule_in.hyp_basis
158 ,p_log_level_rec => p_log_level_rec);
159 fa_debug_pkg.add(fname=>'faxcdb',
160 element=>'rule_in.hyp_total_rsv',
161 value=> rule_in.hyp_total_rsv
162 ,p_log_level_rec => p_log_level_rec);
163 fa_debug_pkg.add(fname=>'faxcdb',
164 element=>'rule_in.hyp_rsv',
165 value=> rule_in.hyp_rsv
166 ,p_log_level_rec => p_log_level_rec);
167 fa_debug_pkg.add(fname=>'faxcdb',
168 element=>'rule_in.hyp_total_ytd',
169 value=> rule_in.hyp_total_ytd
170 ,p_log_level_rec => p_log_level_rec);
171 fa_debug_pkg.add(fname=>'faxcdb',
172 element=>'rule_in.hyp_ytd',
173 value=> rule_in.hyp_ytd
174 ,p_log_level_rec => p_log_level_rec);
175 fa_debug_pkg.add(fname=>'faxcdb',
176 element=>'rule_in.old_adjusted_cost',
177 value=> rule_in.old_adjusted_cost
178 ,p_log_level_rec => p_log_level_rec);
179 fa_debug_pkg.add(fname=>'faxcdb',
180 element=>'rule_in.old_raf',
181 value=> rule_in.old_raf
182 ,p_log_level_rec => p_log_level_rec);
183 fa_debug_pkg.add(fname=>'faxcdb',
184 element=>'rule_in.old_formula_factor',
185 value=> rule_in.old_formula_factor
186 ,p_log_level_rec => p_log_level_rec);
187 fa_debug_pkg.add(fname=>'faxcdb',
188 element=>'rule_in.amortization_start_date',
189 value=> rule_in.amortization_start_date
190 ,p_log_level_rec => p_log_level_rec);
191 fa_debug_pkg.add(fname=>'faxcdb',
192 element=>'rule_in.transaction_header_id',
193 value=> rule_in.transaction_header_id
194 ,p_log_level_rec => p_log_level_rec);
195 fa_debug_pkg.add(fname=>'faxcdb',
196 element=>'rule_in.member_transaction_header_id',
197 value=> rule_in.member_transaction_header_id
198 ,p_log_level_rec => p_log_level_rec);
199 fa_debug_pkg.add(fname=>'faxcdb',
200 element=>'rule_in.transaction_date_entered',
201 value=> rule_in.transaction_date_entered
202 ,p_log_level_rec => p_log_level_rec);
203 fa_debug_pkg.add(fname=>'faxcdb',
204 element=>'rule_in.adj_transaction_header_id',
205 value=> rule_in.adj_transaction_header_id
206 ,p_log_level_rec => p_log_level_rec);
207 fa_debug_pkg.add(fname=>'faxcdb',
208 element=>'rule_in.adj_mem_transaction_header_id',
209 value=> rule_in.adj_mem_transaction_header_id
210 ,p_log_level_rec => p_log_level_rec);
211 fa_debug_pkg.add(fname=>'faxcdb',
212 element=>'rule_in.adj_transaction_date_entered',
213 value=> rule_in.adj_transaction_date_entered
214 ,p_log_level_rec => p_log_level_rec);
215 fa_debug_pkg.add(fname=>'faxcdb',
216 element=>'rule_in.fiscal_year',
217 value=> rule_in.fiscal_year
218 ,p_log_level_rec => p_log_level_rec);
219 fa_debug_pkg.add(fname=>'faxcdb',
220 element=>'rule_in.period_num',
221 value=> rule_in.period_num
222 ,p_log_level_rec => p_log_level_rec);
223 fa_debug_pkg.add(fname=>'faxcdb',
224 element=>'rule_in.period_counter',
225 value=> rule_in.period_counter
226 ,p_log_level_rec => p_log_level_rec);
227 fa_debug_pkg.add(fname=>'faxcdb',
228 element=>'rule_in.proceeds_of_sale',
229 value=> rule_in.proceeds_of_sale
230 ,p_log_level_rec => p_log_level_rec);
231 fa_debug_pkg.add(fname=>'faxcdb',
232 element=>'rule_in.cost_of_removal',
233 value=> rule_in.cost_of_removal
234 ,p_log_level_rec => p_log_level_rec);
235 fa_debug_pkg.add(fname=>'faxcdb',
236 element=>'rule_in.nbv_retired',
237 value=> rule_in.nbv_retired
238 ,p_log_level_rec => p_log_level_rec);
239 fa_debug_pkg.add(fname=>'faxcdb',
240 element=>'rule_in.reduction_rate',
241 value=> rule_in.reduction_rate
242 ,p_log_level_rec => p_log_level_rec);
243 fa_debug_pkg.add(fname=>'faxcdb',
244 element=>'rule_in.eofy_reserve',
245 value=> rule_in.eofy_reserve
246 ,p_log_level_rec => p_log_level_rec);
247 fa_debug_pkg.add(fname=>'faxcdb',
248 element=>'rule_in.adj_reserve',
249 value=> rule_in.adj_reserve
250 ,p_log_level_rec => p_log_level_rec);
251 fa_debug_pkg.add(fname=>'faxcdb',
252 element=>'rule_in.reserve_retired',
253 value=> rule_in.reserve_retired
254 ,p_log_level_rec => p_log_level_rec);
255 fa_debug_pkg.add(fname=>'faxcdb',
256 element=>'rule_in.recognize_gain_loss',
257 value=> rule_in.recognize_gain_loss
258 ,p_log_level_rec => p_log_level_rec);
259 fa_debug_pkg.add(fname=>'faxcdb',
260 element=>'rule_in.tracking_method',
261 value=> rule_in.tracking_method
262 ,p_log_level_rec => p_log_level_rec);
263 fa_debug_pkg.add(fname=>'faxcdb',
264 element=>'rule_in.allocate_to_fully_rsv_flag',
265 value=> rule_in.allocate_to_fully_rsv_flag
266 ,p_log_level_rec => p_log_level_rec);
267 fa_debug_pkg.add(fname=>'faxcdb',
268 element=>'rule_in.allocate_to_fully_ret_flag',
269 value=> rule_in.allocate_to_fully_ret_flag
270 ,p_log_level_rec => p_log_level_rec);
271 fa_debug_pkg.add(fname=>'faxcdb',
272 element=>'rule_in.excess_allocation_option',
273 value=> rule_in.excess_allocation_option
274 ,p_log_level_rec => p_log_level_rec);
275 fa_debug_pkg.add(fname=>'faxcdb',
276 element=>'rule_in.depreciation_option',
277 value=> rule_in.depreciation_option
278 ,p_log_level_rec => p_log_level_rec);
279 fa_debug_pkg.add(fname=>'faxcdb',
280 element=>'rule_in.member_rollup_flag',
281 value=> rule_in.member_rollup_flag
282 ,p_log_level_rec => p_log_level_rec);
283 fa_debug_pkg.add(fname=>'faxcdb',
284 element=>'rule_in.unplanned_amount',
285 value=> rule_in.unplanned_amount
286 ,p_log_level_rec => p_log_level_rec);
287 fa_debug_pkg.add(fname=>'faxcdb',
288 element=>'rule_in.eofy_recoverable_cost',
289 value=> rule_in.eofy_recoverable_cost
290 ,p_log_level_rec => p_log_level_rec);
291 fa_debug_pkg.add(fname=>'faxcdb',
292 element=>'rule_in.eop_recoverable_cost',
293 value=> rule_in.eop_recoverable_cost
294 ,p_log_level_rec => p_log_level_rec);
295 fa_debug_pkg.add(fname=>'faxcdb',
296 element=>'rule_in.eofy_salvage_value',
297 value=> rule_in.eofy_salvage_value
298 ,p_log_level_rec => p_log_level_rec);
299 fa_debug_pkg.add(fname=>'faxcdb',
300 element=>'rule_in.eop_salvage_value',
301 value=> rule_in.eop_salvage_value
302 ,p_log_level_rec => p_log_level_rec);
303 fa_debug_pkg.add(fname=>'faxcdb',
304 element=>'rule_in.used_by_adjustment',
305 value=> rule_in.used_by_adjustment
306 ,p_log_level_rec => p_log_level_rec);
307 fa_debug_pkg.add(fname=>'faxcdb',
308 element=>'rule_in.eofy_flag',
309 value=> rule_in.eofy_flag
310 ,p_log_level_rec => p_log_level_rec);
311 fa_debug_pkg.add(fname=>'faxcdb',
312 element=>'rule_in.apply_reduction_flag',
313 value=> rule_in.apply_reduction_flag
314 ,p_log_level_rec => p_log_level_rec);
315 fa_debug_pkg.add(fname=>'faxcdb',
316 element=>'rule_in.mrc_sob_type_code',
317 value=> rule_in.mrc_sob_type_code
318 ,p_log_level_rec => p_log_level_rec);
319 end if;
320
321 -----------------------------------------------------------
322 -- Copy rule_in to g_rule_in
323 -----------------------------------------------------------
324 g_rule_in := rule_in;
325
326 -----------------------------------------------------------
327 -- Initialize
328 -----------------------------------------------------------
329 g_rule_in.reduction_amount :=0;
330 g_rule_out.new_adjusted_cost := g_rule_in.old_adjusted_cost;
331 g_rule_out.new_raf := g_rule_in.old_raf;
332 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
333 g_rule_in.use_old_adj_cost_flag := null; -- If the calculation uses old adjusted cost
334 -- old adjusted cost, this flag set Y.
335 -- This flag is used by FLAT_EXTENSION.
336 g_rule_in.member_transaction_type_code := null; -- This is for AMORT_ADJ event type
337 g_rule_in.member_proceeds :=0; -- Member Proceeds which group asset is processed.
338
339
340
341 if (rule_in.mrc_sob_type_code = 'R') then
342 if NOT fa_cache_pkg.fazcbcs(X_book => rule_in.book_type_code,
343 p_log_level_rec => p_log_level_rec) then
344 raise faxcdb_err;
345 end if;
346 else
347 -- call the cache for the primary transaction book
348 if NOT fa_cache_pkg.fazcbc(X_book => rule_in.book_type_code,
349 p_log_level_rec => p_log_level_rec) then
350 raise faxcdb_err;
351 end if;
352 end if;
353 -----------------------------------------------------------
354 -- Call Depreciable Basis rules
355 -----------------------------------------------------------
356
357 if (fa_cache_pkg.fazcdbr_record.deprn_basis_rule_id is null) or
358 ((g_rule_in.method_code <> fa_cache_pkg.fazccmt_record.method_code) or
359 (nvl(g_rule_in.life_in_months, -99) <> nvl(fa_cache_pkg.fazccmt_record.life_in_months, -99))) then
360 if fa_cache_pkg.fazccmt(g_rule_in.method_code, g_rule_in.life_in_months) then
361 if p_log_level_rec.statement_level then
362 fa_debug_pkg.add(fname=>'faxcdb',
363 element=>'fazcdbr',
364 value=> 'Called'
365 ,p_log_level_rec => p_log_level_rec);
366 end if;
367 end if;
368 end if;
369
370
371 -- Set flags for depreciable basis rule setup
372 rule_id := fa_cache_pkg.fazcdbr_record.deprn_basis_rule_id;
373 l_rule_name := fa_cache_pkg.fazcdbr_record.rule_name;
374 l_period_update_flag := fa_cache_pkg.fazcdrd_record.period_update_flag;
375 l_subtract_ytd_flag := fa_cache_pkg.fazcdrd_record.subtract_ytd_flag;
376 if p_log_level_rec.statement_level then
377 fa_debug_pkg.add(fname =>'faxcdb',
378 element =>'rule_id',
379 value => rule_id
380 ,p_log_level_rec => p_log_level_rec);
381 fa_debug_pkg.add(fname=>'faxcdb',
382 element=>'l_period_update_flag',
383 value=> l_period_update_flag
384 ,p_log_level_rec => p_log_level_rec);
385 fa_debug_pkg.add(fname=>'faxcdb',
386 element=>'l_subtract_ytd_flag',
387 value=> l_subtract_ytd_flag
388 ,p_log_level_rec => p_log_level_rec);
389 end if;
390
391 -----------------------------
392 -- Validation Check
393 -----------------------------
394 if not SERVER_VALIDATION(p_log_level_rec => p_log_level_rec)
395 then
396 raise faxcdb_err;
397 end if;
398 ------------------------------------------------------------
399 -- Event Type: ADDITION (Additions)
400 ------------------------------------------------------------
401
402 if (g_rule_in.event_type ='ADDITION') then
403 if (g_rule_in.calc_basis = 'NBV') then
404 g_rule_out.new_adjusted_cost :=
405 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv
406 + g_rule_in.current_total_ytd;
407 elsif (g_rule_in.calc_basis = 'COST') then
408 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
409 else
410 raise calc_basis_err;
411 end if;
412
413 g_rule_out.new_raf := 1;
414 g_rule_out.new_formula_factor := 1;
415 end if;
416
417 ------------------------------------------------------------
418 -- Event Type: EXPENSED_ADJ (Expensed Adjustment)
419 ------------------------------------------------------------
420
421 if (g_rule_in.event_type ='EXPENSED_ADJ') then
422 if (g_rule_in.asset_type = 'CAPITALIZED') then
423 if (Upper(g_rule_in.depreciate_flag) like 'Y%') OR
424 (g_rule_in.adjusted_cost =0 AND Upper(g_rule_in.depreciate_flag) NOT like 'Y%') then
425 if (g_rule_in.calc_basis = 'COST') then
426 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
427 g_rule_out.new_formula_factor := 1;
428 elsif g_rule_in.calc_basis = 'NBV' then
429 if (g_rule_in.method_type = 'FORMULA') then
430 -- Bug4169773
431 if nvl(g_rule_in.short_fy_flag, 'NO') = 'NO' then
432 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.hyp_total_rsv
433 + g_rule_in.hyp_total_ytd;
434 else
435 g_rule_out.new_adjusted_cost := g_rule_in.adjusted_cost;
436 end if;
437
438 if g_rule_in.recoverable_cost= 0 then
439 g_rule_out.new_formula_factor := nvl(g_rule_in.old_formula_factor,1);
440 else
441 -- Bug4169773
442 if nvl(g_rule_in.short_fy_flag, 'NO') = 'NO' then
443 g_rule_out.new_formula_factor := 1;
444 else
445 g_rule_out.new_formula_factor := g_rule_in.hyp_basis /
446 g_rule_in.recoverable_cost;
447 end if;
448 end if;
449 elsif (g_rule_in.method_type = 'FLAT') then
450 g_rule_out.new_adjusted_cost :=
451 g_rule_in.recoverable_cost - g_rule_in.hyp_total_rsv
452 + g_rule_in.hyp_total_ytd;
453 g_rule_out.new_formula_factor := 1;
454 else /* other method type */
455 g_rule_out.new_adjusted_cost :=
456 g_rule_in.recoverable_cost - g_rule_in.hyp_total_rsv;
457 g_rule_out.new_formula_factor := 1;
458 end if;
459 else -- unexpected calc_basis
460 raise calc_basis_err;
461 end if; -- End of calc_basis
462 else
463 if (g_rule_in.method_type = 'FLAT') then
464 if (g_rule_in.calc_basis = 'COST') then
465 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
466 elsif g_rule_in.calc_basis = 'NBV' then
467 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
468 else
469 raise calc_basis_err;
470 end if;
471 else /* other method type */
472 g_rule_out.new_adjusted_cost :=
473 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
474 end if;
475 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
476 end if;
477 g_rule_out.new_raf := g_rule_in.old_raf;
478 elsif g_rule_in.asset_type ='GROUP' then
479 -- If the depreciate flag of group asset is NO,
480 -- Group asset is called EXPENSED_ADJ
481 if (g_rule_in.method_type = 'FLAT') then
482 if (g_rule_in.calc_basis = 'COST') then
483 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
484 elsif g_rule_in.calc_basis = 'NBV' then
485 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
486 else -- unexpected calc_basis
487 raise calc_basis_err;
488 end if;
489 else -- other method type
490 g_rule_out.new_adjusted_cost :=
491 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
492 end if; -- End of method type
493 g_rule_out.new_raf := g_rule_in.old_raf;
494 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
495 end if; -- End of Group
496 end if;
497
498 ------------------------------------------------------------
499 -- Event Type: AMORT_ADJ (Amortized Adjustment)
500 ------------------------------------------------------------
501
502 if (g_rule_in.event_type ='AMORT_ADJ') then
503
504 -- Check the member transaction type code
505 if g_rule_in.asset_type ='GROUP'
506 then
507
508 if g_rule_in.member_transaction_header_id is not null then
509 if not (GET_MEM_TRANS_INFO (
510 p_member_transaction_header_id => g_rule_in.member_transaction_header_id,
511 p_mrc_sob_type_code => g_rule_in.mrc_sob_type_code,
512 x_member_transaction_type_code => g_rule_in.member_transaction_type_code,
513 x_member_proceeds => g_rule_in.member_proceeds,
514 x_member_reduction_rate => l_member_reduction_rate
515 ,p_log_level_rec => p_log_level_rec))
516 then
517 raise faxcdb_err;
518 end if;
519 end if; -- member_transaction_header_id is not null
520
521 if p_log_level_rec.statement_level then
522 fa_debug_pkg.add(fname =>'faxcdb',
523 element =>'member_transaction_type_code',
524 value => g_rule_in.member_transaction_type_code
525 ,p_log_level_rec => p_log_level_rec);
526 fa_debug_pkg.add(fname =>'faxcdb',
527 element =>'member_proceeds',
528 value => g_rule_in.member_proceeds
529 ,p_log_level_rec => p_log_level_rec);
530 fa_debug_pkg.add(fname =>'faxcdb',
531 element =>'l_member_reduction_rate',
532 value => l_member_reduction_rate
533 ,p_log_level_rec => p_log_level_rec);
534 fa_debug_pkg.add(fname =>'faxcdb',
535 element =>'l_member_transaction_flag',
536 value => l_member_transaction_flag
537 ,p_log_level_rec => p_log_level_rec);
538 end if;
539
540 -- If this transaction is member's transaction,
541 -- replace the group default reduction rate
542 -- to member transaction reduction rate
543 if g_rule_in.member_transaction_header_id is not null then
544 g_rule_in.reduction_rate := l_member_reduction_rate;
545 if p_log_level_rec.statement_level then
546 fa_debug_pkg.add(fname =>'faxcdb',
547 element =>'Updated reduction_rate',
548 value => g_rule_in.reduction_rate
549 ,p_log_level_rec => p_log_level_rec);
550 end if;
551 end if;
552 end if; -- End of checking the member transaction type code
553
554 if (g_rule_in.asset_type ='GROUP'
555 or (g_rule_in.asset_type <> 'GROUP' and g_rule_in.tracking_method='ALLOCATE')
556 )
557 and nvl(g_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
558 --Added for 7713533
559 and (nvl(g_rule_in.method_type, 'NULL') <> 'CALCULATED')
560 --7713533 ends here
561 then
562 -- When member assets are retired, the group asset and allocated member asset
563 -- are processed as follows.
564
565 if g_rule_in.calc_basis = 'NBV' then
566 --
567 -- Bug3463933: Added condition to set adjusted_cost to 0 if cost is 0.
568 --
569 if g_rule_in.cost = 0 then
570 g_rule_out.new_adjusted_cost := 0;
571 elsif g_rule_in.recognize_gain_loss like 'N%' then
572 -- Do Not Recognaize Gain and Loss
573 g_rule_out.new_adjusted_cost :=
574 g_rule_in.recoverable_cost - g_rule_in.adjustment_amount
575 - g_rule_in.eofy_reserve - nvl(g_rule_in.member_proceeds,0);
576 else -- Recog Gain/Loss
577 g_rule_out.new_adjusted_cost :=
578 g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
579 end if;
580 elsif g_rule_in.calc_basis = 'COST' then
581 if g_rule_in.cost = 0 then
582 g_rule_out.new_adjusted_cost := 0;
583 else
584 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
585 end if;
586 else -- unexpected calc_basis
587 raise calc_basis_err;
588 end if; -- End of calc_basis
589
590 g_rule_out.new_raf := g_rule_in.old_raf;
591 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
592
593 else -- Normal Adjustment
594 if (g_rule_in.calc_basis = 'COST') then
595 if (g_rule_in.method_type = 'FLAT') then
596 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
597 g_rule_out.new_raf := 1;
598 elsif (g_rule_in.method_type = 'PRODUCTION') then
599 if g_rule_in.recoverable_cost = 0 then
600 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
601 else
602 g_rule_out.new_adjusted_cost :=
603 g_rule_in.recoverable_cost - g_rule_in.current_rsv;
604 end if;
605 g_rule_out.new_raf := 1;
606 else /* other method type */
607 if g_rule_in.recoverable_cost = 0 then
608 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
609 g_rule_out.new_raf := nvl(g_rule_in.old_raf,1);
610 else
611 if (g_rule_in.method_type = 'CALCULATED') then
612 -- Japan Tax Phase3 Adjusted_cost after reinstatement in extended
613 -- period is based on cost instead of recoverable cost.
614 if (nvl(g_rule_in.transaction_flag,'X') = 'ES') then
615 g_rule_out.new_adjusted_cost :=
616 g_rule_in.cost - g_rule_in.current_total_rsv - nvl(g_rule_in.impairment_reserve,0) - 1;
617 else
618 g_rule_out.new_adjusted_cost :=
619 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv - nvl(g_rule_in.impairment_reserve,0); -- Added NVL for bug# 5079543
620 end if;
621 else
622 g_rule_out.new_adjusted_cost :=
623 g_rule_in.recoverable_cost - g_rule_in.current_rsv;
624 end if;
625 -- Bug 6945270: raf is 1 for JP-STL-EXTND method
626 if (nvl(g_rule_in.transaction_flag,'X') = 'ES') then
627 g_rule_out.new_raf := 1;
628 else
629 g_rule_out.new_raf := (g_rule_in.recoverable_cost -
630 g_rule_in.hyp_total_rsv)/g_rule_in.recoverable_cost;
631 end if;
632 end if;
633 end if;
634 g_rule_out.new_formula_factor := 1;
635
636 elsif g_rule_in.calc_basis = 'NBV' then
637 if (g_rule_in.method_type = 'FLAT') then
638 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
639 g_rule_out.new_raf := 1;
640 g_rule_out.new_formula_factor := 1;
641 elsif (g_rule_in.method_type = 'FORMULA') then
642 if g_rule_in.recoverable_cost = 0 then
643 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
644 g_rule_out.new_raf := nvl(g_rule_in.old_raf,1);
645 g_rule_out.new_formula_factor := nvl(g_rule_in.old_formula_factor,1);
646 else
647
648 -- Bug 6665510: FP: Japan Tax Reform Project (Start)
649 if nvl(fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag,'NO') = 'YES' then
650
651 select bk.deprn_method_code, bk.salvage_value, bk.cost -- bug 6378955 (added salvage value)
652 into l_old_method_code, l_old_salvage_value, l_old_cost -- l_old_cost added for Japan overlapped
653 from FA_BOOKS bk
654 where bk.asset_id = g_rule_in.asset_id
655 and bk.book_type_code = g_rule_in.book_type_code
656 and bk.transaction_header_id_out is null;
657
658 if l_old_cost <> g_rule_in.cost then
659 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv
660 + g_rule_in.current_total_ytd;
661 elsif g_rule_in.method_code <> l_old_method_code and nvl(l_old_salvage_value,0) <> nvl(g_rule_in.salvage_value,0) then -- else part added for bug 6378955
662 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv
663 + g_rule_in.current_total_ytd; -- Changed for bug 6780728
664 elsif g_rule_in.method_code = l_old_method_code then
665 g_rule_out.new_adjusted_cost :=
666 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
667 end if;
668
669 else
670 g_rule_out.new_adjusted_cost :=
671 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
672 end if;
673 -- Bug 6665510: FP: Japan Tax Reform Project (End)
674
675 -- Bug4169773
676 if nvl(g_rule_in.short_fy_flag, 'NO') = 'NO' then
677 g_rule_out.new_formula_factor := 1;
678 g_rule_out.new_raf := 1;
679 else
680 g_rule_out.new_raf := (g_rule_in.recoverable_cost -
681 g_rule_in.hyp_total_rsv)/g_rule_in.recoverable_cost;
682 g_rule_out.new_formula_factor := g_rule_in.hyp_basis
683 / g_rule_in.recoverable_cost;
684 end if;
685 end if;
686 else /* other method types */
687 if g_rule_in.recoverable_cost = 0 then
688 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
689 else
690 g_rule_out.new_adjusted_cost :=
691 g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
692 end if;
693 g_rule_out.new_raf := 1;
694 g_rule_out.new_formula_factor := 1;
695 end if; --Method type
696 else -- unexpected calc_basis
697 raise calc_basis_err;
698 end if; -- Calc Basis
699
700 end if; -- Normal Adjustment
701 end if;
702
703 ------------------------------------------------------------
704 -- Event Type: AMORT_ADJ2 (Amortized Adjustment 2)
705 --
706 -- For Back-dated Adjustment
707 -- This event type is Obsoleted.
708 ------------------------------------------------------------
709
710 if (g_rule_in.event_type ='AMORT_ADJ2') then
711 g_rule_out.new_adjusted_cost := g_rule_in.old_adjusted_cost;
712 g_rule_out.new_raf := g_rule_in.old_raf;
713 g_rule_out.new_formula_factor := g_rule_out.new_formula_factor;
714 end if;
715
716 ------------------------------------------------------------
717 -- Event Type: AMORT_ADJ3 (Amortized Adjustment 3)
718 --
719 -- For Back-dated Adjustment
720 ------------------------------------------------------------
721
722 if (g_rule_in.event_type ='AMORT_ADJ3') then
723 -- Bug 6665510: FP: Japan Tax Reform Project
724 -- (Changed below if bcoz Reinstament was causing change in Adjusted_cost,
725 -- and here adjusted_cost change is restrcited for guarantee methods)
726 if (g_rule_in.method_type = 'FORMULA' and g_rule_in.calc_basis ='NBV'
727 AND nvl(fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag,'NO') <> 'YES'
728 ) then
729 -- Bug 5212364
730 if (g_rule_in.current_rsv <> 0) then
731 g_rule_out.new_adjusted_cost :=
732 g_rule_in.recoverable_cost - g_rule_in.current_rsv;
733 else
734 g_rule_out.new_adjusted_cost :=
735 g_rule_in.recoverable_cost - nvl(g_rule_in.eofy_reserve,0);
736 end if;
737 else
738 -- Restructured this else part for bug 6780736
739 select bk.deprn_method_code
740 into l_old_method_code
741 from FA_BOOKS bk
742 where bk.asset_id = g_rule_in.asset_id
743 and bk.book_type_code = g_rule_in.book_type_code
744 and bk.transaction_header_id_out is null;
745
746 if g_rule_in.method_code = l_old_method_code then
747 g_rule_out.new_adjusted_cost := g_rule_in.adjusted_cost;
748 g_rule_in.use_old_adj_cost_flag :='Y';
749 end if;
750 end if;
751 g_rule_out.new_raf := g_rule_in.old_raf;
752 g_rule_out.new_formula_factor := g_rule_out.new_formula_factor;
753 end if;
754
755 ------------------------------------------------------------
756 -- Event Type: RETIREMENT (Retirements)
757 --
758 ------------------------------------------------------------
759
760 if (g_rule_in.event_type ='RETIREMENT') then
761
762 if g_rule_in.recognize_gain_loss like 'N%'
763 then -- Do Not Recognaize Gain and Loss
764 if g_rule_in.calc_basis = 'COST'
765 then -- Cost Base
766 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
767 elsif g_rule_in.calc_basis = 'NBV' then
768 g_rule_out.new_adjusted_cost :=
769 g_rule_in.recoverable_cost - g_rule_in.eofy_reserve
770 - (g_rule_in.nbv_retired);
771 else -- unexpected calc_basis
772 raise calc_basis_err;
773 end if;
774 elsif g_rule_in.recognize_gain_loss like 'Y%'
775 and g_rule_in.calc_basis = 'NBV'
776 and g_rule_in.group_asset_id is not null
777 then -- Member asset NBV base and Recog Gain/Loss
778 g_rule_out.new_adjusted_cost :=
779 g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
780 else -- Member Cost base and standalone asset
781 if g_rule_in.cost = 0 then
782 g_rule_out.new_adjusted_cost := 0;
783 else
784
785 /* Fix for Bug #3901978. Replaced cost w/ old_cost.
786 g_rule_out.new_adjusted_cost :=
787 g_rule_in.old_adjusted_cost*
788 (1- g_rule_in.adjustment_amount/g_rule_in.cost);
789 */
790 -- Fix for Bug #6364053/6365756. Catch the case where this is 0.
791 if (g_rule_in.old_cost = 0) then
792
793 g_rule_out.new_adjusted_cost :=
794 g_rule_in.recoverable_cost -
795 nvl(g_rule_in.impairment_reserve,0);
796 -- Japan Tax Phase3
797 elsif (nvl(g_rule_in.transaction_flag,'X') = 'ES') then
798 g_rule_out.new_adjusted_cost :=
799 (g_rule_in.old_adjusted_cost + 1) *
800 (1- g_rule_in.adjustment_amount/g_rule_in.old_cost) - 1;
801 else
802 g_rule_out.new_adjusted_cost :=
803 g_rule_in.old_adjusted_cost*
804 (1- g_rule_in.adjustment_amount/g_rule_in.old_cost);
805
806 end if;
807
808 g_rule_in.use_old_adj_cost_flag :='Y';
809
810 end if;
811 end if; -- End Gain and Loss option
812
813 g_rule_out.new_raf := g_rule_in.old_raf;
814 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
815 end if; -- End Retirement
816
817 if p_log_level_rec.statement_level then
818 fa_debug_pkg.add('faxcdb', 'After deprn', 'Begin', p_log_level_rec);
819 fa_debug_pkg.add('faxcdb', '+++ Event Type : ', g_rule_in.event_type, p_log_level_rec);
820 fa_debug_pkg.add('faxcdb', '+++ Calc Basis : ', g_rule_in.calc_basis, p_log_level_rec);
821 end if;
822
823 ------------------------------------------------------------
824 -- Event Type: AFTER_DEPRN (After Depreciation)
825 --
826 -- Recalculate Adjusted Cost After Depreciation of Fiscal
827 -- Year End.
828 ------------------------------------------------------------
829 if (g_rule_in.event_type ='AFTER_DEPRN') then
830
831 -- Calculate Adjusted Cost
832 IF g_rule_in.eofy_flag ='Y' OR l_period_update_flag='Y' then
833 if (g_rule_in.calc_basis = 'COST') then
834 g_rule_out.new_adjusted_cost :=g_rule_in.old_adjusted_cost;
835 g_rule_in.use_old_adj_cost_flag :='Y';
836
837 elsif g_rule_in.calc_basis = 'NBV' then
838 -- Bug 6665510: FP: Japan Tax Reform Project (Start)
839 if p_log_level_rec.statement_level then
840 fa_debug_pkg.add('faxcdb', '+++ Guarantee Flag : ', fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag,
841 p_log_level_rec);
842 end if;
843
844 if nvl(fa_cache_pkg.fazccmt_record.guarantee_rate_method_flag,'NO') = 'YES' then
845
846 if p_log_level_rec.statement_level then
847 fa_debug_pkg.add('faxcdb', '+++ Inside Guarantee Logic', 'YES', p_log_level_rec);
848 end if;
849
850 SELECT rate_in_use, deprn_method_code -- Added deprn_method_code for bug fix 6780736
851 INTO l_rate_in_use, l_old_method_code
852 FROM fa_books
853 WHERE asset_id = g_rule_in.asset_id
854 AND book_type_code = g_rule_in.book_type_code
855 AND date_ineffective is null;
856
857 if p_log_level_rec.statement_level then
858 fa_debug_pkg.add('faxcdb', '+++ Revised Rate : ', fa_cache_pkg.fazcfor_record.revised_rate, p_log_level_rec);
859 fa_debug_pkg.add('faxcdb', '+++ FA_Books.Rate : ', l_rate_in_use, p_log_level_rec);
860 fa_debug_pkg.add('faxcdb', '+++ FA_Books.deprn_method : ', l_old_method_code, p_log_level_rec);
861 end if;
862
863 -- Added if clause for bug fix 6780736
864 if g_rule_in.method_code <> l_old_method_code then
865 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
866 elsif fa_cache_pkg.fazcfor_record.revised_rate = l_rate_in_use then
867 Null; -- Dont change adjusted cost.
868 else
869 if p_log_level_rec.statement_level then
870 fa_debug_pkg.add('faxcdb', '+++ ORIGINAL RATE', 'YES', p_log_level_rec);
871 end if;
872
873 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
874 end if;
875
876 else -- guarantee rate is 'NO'
877
878 if p_log_level_rec.statement_level then
879 fa_debug_pkg.add('faxcdb', '+++ Outside Guarantee Logic', 'YES', p_log_level_rec);
880 end if;
881
882 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv;
883
884 end if; -- guarantee rate
885 -- Bug 6665510: FP: Japan Tax Reform Project (End)
886
887 else -- unexpected calc_basis
888 raise calc_basis_err;
889 end if;
890 END IF; -- End deprn_end_perd_flag ='Y' OR l_period_update_flag='Y'
891 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
892 g_rule_out.new_raf := g_rule_in.old_raf;
893 end if; -- End event type
894
895 -------------------------------------------------------------
896 -- Event Type: AFTER_DEPRN2 (After Depreciation 2)
897 --
898 -- Recalculate Formula Factor after event type 'AFTER_DEPRN'.
899 -- When method type is 'FORMULA',calculation basis is 'NBV',
900 -- raf is not 1 and formula factor is not 1,
901 -- call faxdfcc and call AFTER_DEPRN2 from faxdfcc.
902 -------------------------------------------------------------
903
904 if (g_rule_in.event_type ='AFTER_DEPRN2') then
905
906 if g_rule_in.recoverable_cost =0 or g_rule_in.old_adjusted_cost =0 then
907 g_rule_out.new_formula_factor := nvl(g_rule_in.old_formula_factor,1);
908 else
909 -- Bug4169773
910 if nvl(g_rule_in.short_fy_flag, 'NO') = 'NO' then
911 g_rule_out.new_formula_factor := 1;
912 else
913 g_rule_out.new_formula_factor := g_rule_in.adjusted_cost *
914 (g_rule_in.recoverable_cost - g_rule_in.hyp_total_rsv)
915 / g_rule_in.recoverable_cost / g_rule_in.old_adjusted_cost;
916 end if;
917 end if;
918
919 g_rule_out.new_adjusted_cost :=g_rule_in.old_adjusted_cost;
920 g_rule_out.new_raf := g_rule_in.old_raf;
921 end if;
922
923 -------------------------------------------------------------
924 -- Event Type: DEPRECIATE_FLAG_ADJ (IDLE Asset Control)
925 -- When depreciate flag is changed, this event is called.
926 -------------------------------------------------------------
927
928 if (g_rule_in.event_type ='DEPRECIATE_FLAG_ADJ') then
929
930 if (g_rule_in.method_type = 'FORMULA') THEN
931 g_rule_out.new_adjusted_cost := g_rule_in.adjusted_cost;
932 g_rule_out.new_raf := g_rule_in.old_raf;
933 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
934 END IF;
935
936 if (Upper(g_rule_in.depreciate_flag) like 'Y%'
937 AND (g_rule_in.calc_basis ='NBV' AND
938 (g_rule_in.method_type = 'TABLE' OR g_rule_in.method_type = 'FLAT')))
939 THEN
940
941 l_asset_id := g_rule_in.asset_id;
942 l_book_type_code := g_rule_in.book_type_code;
943
944 select count(*)
945 into l_last_trx_count
946 from fa_books bks,
947 fa_deprn_periods dp
948 where bks.asset_id = l_asset_id
949 and bks.book_type_code = l_book_type_code
950 and bks.date_ineffective is null
951 and dp.book_type_code = l_book_type_code
952 and bks.date_effective between
953 dp.period_open_date and nvl(dp.period_close_date, sysdate)
954 and dp.fiscal_year = fa_cache_pkg.fazcbc_record.current_fiscal_year;
955
956 IF (l_last_trx_count =0) THEN
957 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.eofy_reserve;
958 ELSE
959 g_rule_out.new_adjusted_cost := g_rule_in.old_adjusted_cost;
960 g_rule_in.use_old_adj_cost_flag :='Y';
961 END IF;
962
963 g_rule_out.new_raf := g_rule_in.old_raf;
964 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
965
966 ELSE
967 g_rule_out.new_adjusted_cost := g_rule_in.old_adjusted_cost;
968 g_rule_out.new_raf := g_rule_in.old_raf;
969 g_rule_out.new_formula_factor := 1;
970
971 g_rule_in.use_old_adj_cost_flag :='Y';
972 END IF;
973
974 END IF;
975
976 ----------------------------------------------------------------
977 -- Event Type: UNPLANNED_ADJ (Unplanned Depreciation)
978 ----------------------------------------------------------------
979 if (g_rule_in.event_type ='UNPLANNED_ADJ') THEN
980 /*IF g_rule_in.old_raf <>1 or (g_rule_in.amortization_start_date is not null
981 and nvl(g_rule_in.life_in_months, 0) > 0) THEN */
982 -- Bug 7174365 Modified If condition to unchange adjusted cost for flat rate cost basis method.
983 IF
984 g_rule_in.amortization_start_date is null or
985 (g_rule_in.amortization_start_date is not null
986 and fa_cache_pkg.fazccmt_record.rate_source_rule = fa_std_types.FAD_RSR_FLAT
987 and fa_cache_pkg.fazccmt_record.deprn_basis_rule = fa_std_types.FAD_DBR_COST)
988 THEN
989 g_rule_out.new_adjusted_cost := g_rule_in.old_adjusted_cost;
990 g_rule_in.use_old_adj_cost_flag :='Y';
991
992 ELSE /* Bug 7174365 For all other methods caluculate the new adjusted cost. */
993 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost - g_rule_in.current_total_rsv -
994 g_rule_in.unplanned_amount - nvl(g_rule_in.impairment_reserve,0); -- Added NVL for bug# 5079543
995
996 END IF;
997
998 g_rule_out.new_raf := g_rule_in.old_raf;
999 g_rule_out.new_formula_factor := g_rule_in.old_formula_factor;
1000 end if; -- End UNPLANNED_ADJ
1001
1002 ----------------------------------------------------------------
1003 -- Event Type: INITIAL_ADDITION
1004 --
1005 -- This event type calculates adjusted_cost at the period
1006 -- of DPIS.
1007 -- This adjusted cost is defaulted by the calculation of
1008 -- rate adjustment factor.
1009 ----------------------------------------------------------------
1010
1011 if (g_rule_in.event_type ='INITIAL_ADDITION') THEN
1012 g_rule_out.new_adjusted_cost := g_rule_in.recoverable_cost;
1013 g_rule_out.new_raf := 1;
1014 g_rule_out.new_formula_factor :=1;
1015 end if; -- End of INITIAL_ADDITION
1016
1017 ------------------------------------------------------------
1018 -- Call Depreciable Basis Rule
1019 ------------------------------------------------------------
1020
1021 l_rule_formula :=
1022 'BEGIN '||fa_cache_pkg.fazcdbr_record.program_name||';
1023
1024 exception
1025 when others then
1026 fa_srvr_msg.add_sql_error
1027 (calling_fn => '''||fa_cache_pkg.fazcdbr_record.program_name||'''
1028 ,p_log_level_rec => Null);
1029 raise;
1030 END;';
1031 --removed p_log_level_rec and added null for bug 6345540
1032 -- Added for bug# 5171343
1033 if not fa_utils_pkg.faxrnd(g_rule_out.new_adjusted_cost, rule_in.book_type_code) then
1034 fa_srvr_msg.add_message(calling_fn => 'faxcdb',
1035 p_log_level_rec => p_log_level_rec);
1036 return (FALSE);
1037 end if;
1038
1039 -----------------------------------------------
1040 -- Run formula
1041 -----------------------------------------------
1042 if (fa_cache_pkg.fazcdbr_record.program_name is not null) then
1043 if p_log_level_rec.statement_level then
1044
1045 fa_debug_pkg.add(fname=>'faxcdb',
1046 element=>'Rule Name before rule logic',
1047 value=> l_rule_name
1048 ,p_log_level_rec => p_log_level_rec);
1049
1050 fa_debug_pkg.add(fname=>'faxcdb',
1051 element=>'new_adjusted_cost before rule logic',
1052 value=> g_rule_out.new_adjusted_cost
1053 ,p_log_level_rec => p_log_level_rec);
1054 fa_debug_pkg.add(fname=>'faxcdb',
1055 element=>'new_raf before rule logic',
1056 value=> g_rule_out.new_raf
1057 ,p_log_level_rec => p_log_level_rec);
1058 fa_debug_pkg.add(fname=>'faxcdb',
1059 element=>'new_formula_factor before rule logic',
1060 value=> g_rule_out.new_formula_factor
1061 ,p_log_level_rec => p_log_level_rec);
1062
1063 end if;
1064
1065 ----------------------------------------------------------
1066 -- SEEDED rules are called to use parameters.
1067 -- and not seeded rules are called to use glbal variables
1068 -- as parameters.
1069 ----------------------------------------------------------
1070 -- Bug 6665510: FP: Japan Tax Reform Project
1071 if l_rule_name in ('USE TRANSACTION PERIOD BASIS','PERIOD END BALANCE', 'ENERGY PERIOD END BALANCE' ,'DUAL RATE EVALUATION')
1072 then
1073 if g_rule_in.calc_basis ='NBV' or l_rule_name = 'ENERGY PERIOD END BALANCE' then
1074
1075 FA_CALC_DEPRN_BASIS2_PKG.NON_STRICT_FLAT
1076 (
1077 px_rule_in => g_rule_in,
1078 px_rule_out => g_rule_out
1079
1080 ,p_log_level_rec => p_log_level_rec);
1081 end if;
1082 elsif l_rule_name = 'FLAT RATE EXTENSION'
1083 then
1084 FA_CALC_DEPRN_BASIS2_PKG.FLAT_EXTENSION
1085 (
1086 px_rule_in => g_rule_in,
1087 px_rule_out => g_rule_out
1088
1089 ,p_log_level_rec => p_log_level_rec);
1090 elsif l_rule_name = 'PERIOD END AVERAGE'
1091 then
1092 FA_CALC_DEPRN_BASIS2_PKG.PERIOD_AVERAGE
1093 (
1094 px_rule_in => g_rule_in,
1095 px_rule_out => g_rule_out
1096
1097 ,p_log_level_rec => p_log_level_rec);
1098 -- elsif l_rule_name = 'YEAR TO DATE AVERAGE'
1099 elsif l_rule_name in ('YEAR TO DATE AVERAGE', 'YEAR TO DATE AVERAGE WITH HALF YEAR RULE')
1100 then
1101 FA_CALC_DEPRN_BASIS2_PKG.YTD_AVERAGE
1102 (
1103 px_rule_in => g_rule_in,
1104 px_rule_out => g_rule_out
1105
1106 ,p_log_level_rec => p_log_level_rec);
1107 elsif l_rule_name = 'YEAR END BALANCE WITH POSITIVE REDUCTION AMOUNT'
1108 then
1109 FA_CALC_DEPRN_BASIS2_PKG.POSITIVE_REDUCTION
1110 (
1111 px_rule_in => g_rule_in,
1112 px_rule_out => g_rule_out
1113
1114 ,p_log_level_rec => p_log_level_rec);
1115 elsif l_rule_name = 'YEAR END BALANCE WITH HALF YEAR RULE'
1116 then
1117 FA_CALC_DEPRN_BASIS2_PKG.HALF_YEAR
1118 (
1119 px_rule_in => g_rule_in,
1120 px_rule_out => g_rule_out
1121
1122 ,p_log_level_rec => p_log_level_rec);
1123 elsif l_rule_name = 'BEGINNING PERIOD'
1124 then
1125 FA_CALC_DEPRN_BASIS2_PKG.BEGINNING_PERIOD
1126 (
1127 px_rule_in => g_rule_in,
1128 px_rule_out => g_rule_out
1129
1130 ,p_log_level_rec => p_log_level_rec);
1131 else
1132 IF (p_log_level_rec.statement_level) THEN
1133 fa_rx_util_pkg.debug('faxcdb: ' || l_rule_formula);
1134 END IF;
1135
1136 execute immediate l_rule_formula;
1137 end if; -- End of rule procedure call
1138
1139 end if; -- End of program name is not null
1140
1141 -----------------------------------------------------------
1142 -- Apply reduction amount to new adjusted cost
1143 -----------------------------------------------------------
1144 if (g_rule_in.calc_basis ='COST'
1145 and g_rule_in.event_type not in
1146 ('AFTER_DEPRN2','AMORT_ADJ2','AMORT_ADJ3','UNPLANNED_ADJ','DEPRECIATE_FLAG_ADJ','INITIAL_ADDITION'))
1147 or
1148 (g_rule_in.calc_basis ='NBV'
1149 and g_rule_in.event_type not in
1150 ('AFTER_DEPRN2','AMORT_ADJ2','AMORT_ADJ3','UNPLANNED_ADJ','INITIAL_ADDITION'))
1151 then
1152 g_rule_out.new_adjusted_cost := g_rule_out.new_adjusted_cost
1153 - nvl(g_rule_in.reduction_amount,0);
1154 end if;
1155
1156 if p_log_level_rec.statement_level then
1157
1158 fa_debug_pkg.add(fname=>'faxcdb',
1159 element=>'reduction_amount',
1160 value=> g_rule_in.reduction_amount
1161 ,p_log_level_rec => p_log_level_rec);
1162 end if;
1163
1164 -----------------------------------------------------------
1165 -- Year End Balance type:
1166 -- Treatement of Do not gain loss retirement.
1167 -----------------------------------------------------------
1168
1169 if l_subtract_ytd_flag='Y' then
1170 if (g_rule_in.calc_basis = 'NBV') and g_rule_in.recognize_gain_loss like 'N%' then
1171
1172 if g_rule_in.event_type not in
1173 ('AFTER_DEPRN','AFTER_DEPRN2','AMORT_ADJ2','AMORT_ADJ3','UNPLANNED_ADJ','INITIAL_ADDITION')
1174 then
1175
1176 -- Calcluation of proceeds
1177 if not CALC_PROCEEDS (
1178 p_asset_id => g_rule_in.asset_id,
1179 p_asset_type => g_rule_in.asset_type,
1180 p_book_type_code => g_rule_in.book_type_code,
1181 p_period_counter => g_rule_in.period_counter,
1182 p_mrc_sob_type_code => g_rule_in.mrc_sob_type_code,
1183 x_ltd_proceeds => l_ltd_proceeds,
1184 x_ytd_proceeds => l_ytd_proceeds
1185 ,p_log_level_rec => p_log_level_rec)
1186 then
1187 raise faxcdb_err;
1188 end if; -- End of call CALC_PROCEEDS
1189
1190 -- Calculation of retired cost
1191 if not CALC_RETIRED_COST (
1192 p_event_type => g_rule_in.event_type,
1193 p_asset_id => g_rule_in.asset_id,
1194 p_asset_type => g_rule_in.asset_type,
1195 p_book_type_code => g_rule_in.book_type_code,
1196 p_fiscal_year => g_rule_in.fiscal_year,
1197 p_period_num => g_rule_in.period_num,
1198 p_adjustment_amount => g_rule_in.adjustment_amount,
1199 p_ltd_ytd_flag => 'YTD',
1200 p_mrc_sob_type_code => g_rule_in.mrc_sob_type_code,
1201 x_retired_cost => l_retired_cost
1202 ,p_log_level_rec => p_log_level_rec)
1203 then
1204 raise faxcdb_err;
1205 end if;
1206
1207 if p_log_level_rec.statement_level then
1208
1209 fa_debug_pkg.add(fname=>'faxcdb',
1210 element=>'l_retired_cost',
1211 value=> l_retired_cost
1212 ,p_log_level_rec => p_log_level_rec);
1213 fa_debug_pkg.add(fname=>'faxcdb',
1214 element=>'l_ytd_proceeds',
1215 value=> l_ytd_proceeds
1216 ,p_log_level_rec => p_log_level_rec);
1217 end if;
1218
1219 g_rule_out.new_adjusted_cost := nvl(g_rule_out.new_adjusted_cost,0) + nvl(l_retired_cost,0)
1220 - nvl(l_ytd_proceeds,0);
1221
1222 end if; -- End of event type
1223 end if; -- End of calc_basis
1224 end if; -- End of subtract_ytd_flag
1225
1226 -----------------------------------------------------------
1227 -- Set Output variables
1228 -----------------------------------------------------------
1229 rule_out.new_adjusted_cost := nvl(g_rule_out.new_adjusted_cost,g_rule_in.old_adjusted_cost);
1230 rule_out.new_raf := nvl(g_rule_out.new_raf,g_rule_in.old_raf);
1231 rule_out.new_formula_factor := nvl(g_rule_out.new_formula_factor,g_rule_in.old_formula_factor);
1232 rule_out.new_deprn_rounding_flag := g_rule_out.new_deprn_rounding_flag;
1233
1234 -----------------------------------------------------------
1235 -- Bug4267005: Reinstating validation
1236 -- Checking raf to make sure that it falls in between 0 and 1
1237 -----------------------------------------------------------
1238 if g_rule_in.event_type like 'AMORT_ADJ%' then
1239
1240 if (rule_out.new_raf < 0 OR rule_out.new_raf > 1) then
1241 fa_debug_pkg.add('faxcdb', 'Rate adjustment factor ',
1242 'Out of valid range'
1243 ,p_log_level_rec => p_log_level_rec);
1244 fa_debug_pkg.add('faxcdb', 'rule_out.new_raf',
1245 rule_out.new_raf
1246 ,p_log_level_rec => p_log_level_rec);
1247 FA_SRVR_MSG.ADD_MESSAGE
1248 (CALLING_FN => l_calling_fn,
1249 NAME=>'FA_AMT_RAF_OUT_OF_RANGE'
1250 ,p_log_level_rec => p_log_level_rec);
1251 raise faxcdb_err;
1252 end if;
1253 end if;
1254
1255 ------------------------------------------------------------
1256 -- Debug output paramters
1257 ------------------------------------------------------------
1258
1259 if p_log_level_rec.statement_level then
1260 fa_debug_pkg.add(fname=>'faxcdb',
1261 element=>'rule_out.new_adjusted_cost',
1262 value=> rule_out.new_adjusted_cost
1263 ,p_log_level_rec => p_log_level_rec);
1264 fa_debug_pkg.add(fname=>'faxcdb',
1265 element=>'rule_out.new_raf',
1266 value=> rule_out.new_raf
1267 ,p_log_level_rec => p_log_level_rec);
1268 fa_debug_pkg.add(fname=>'faxcdb',
1269 element=>'rule_out.new_formula_factor',
1270 value=> rule_out.new_formula_factor
1271 ,p_log_level_rec => p_log_level_rec);
1272 fa_debug_pkg.add(fname=>'faxcdb',
1273 element=>'rule_out.new_deprn_rounding_flag',
1274 value=> rule_out.new_deprn_rounding_flag
1275 ,p_log_level_rec => p_log_level_rec);
1276 end if;
1277
1278 -----------------------------------------------------------
1279 -- Call FA_TRACK_MEMBER_PVT.UPDATE_DEPRN_BASIS
1280 -----------------------------------------------------------
1281
1282 IF g_rule_in.tracking_method ='ALLOCATE' and g_rule_in.asset_type='GROUP'
1283 and (g_rule_in.event_type not in ('AMORT_ADJ3','AFTER_DEPRN','AFTER_DEPRN2','INITIAL_ADDITION')
1284 or (g_rule_in.event_type ='AFTER_DEPRN' and (g_rule_in.eofy_flag ='Y' or l_period_update_flag='Y')))
1285 then
1286 IF NOT fa_track_member_pvt.update_deprn_basis
1287 (p_group_rule_in => g_rule_in,
1288 p_apply_reduction_flag => g_rule_in.apply_reduction_flag,
1289 p_mode => g_rule_in.used_by_adjustment
1290 ,p_log_level_rec => p_log_level_rec)
1291 THEN
1292 if p_log_level_rec.statement_level then
1293 fa_debug_pkg.add(fname=>'faxcdb',
1294 element=>'fa_track_member_pvt.update_deprn_basis',
1295 value=> 'False'
1296 ,p_log_level_rec => p_log_level_rec);
1297 end if;
1298
1299 raise faxcdb_err;
1300 END IF;
1301 end if;
1302
1303 return true;
1304
1305 exception
1306 when faxcdb_err then
1307 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1308 ,p_log_level_rec => p_log_level_rec);
1309 return (false);
1310 when calc_basis_err then
1311 if p_log_level_rec.statement_level then
1312 fa_debug_pkg.add(fname=>l_calling_fn,
1313 element=>'g_rule_in.calc_basis',
1314 value=> g_rule_in.calc_basis
1315 ,p_log_level_rec => p_log_level_rec);
1316 end if;
1317
1318 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1319 ,p_log_level_rec => p_log_level_rec);
1320 return (false);
1321
1322 when others then
1323 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
1324 ,p_log_level_rec => p_log_level_rec);
1325 return (false);
1326 end faxcdb;
1327
1328 ------------------------------------------------------------------------------
1329 -- Function: CALC_REDUCTION_AMOUNT
1330 --
1331 -- This function queries the reduction rate's applying amount
1332 --
1333 -- p_asset_id : Asset Id
1334 -- p_group_asset_id : Group Asset Id
1335 -- p_asset_type : Asset Type
1336 -- p_book_type_code : Book Type Code
1337 -- p_period_counter : Period Counter
1338 -- p_transaction_date : Processing transaction date
1339 -- p_half_year_rule_flag : Y- Output first and second half amount
1340 -- x_change_in_cost : Changed cost for a year with only applying
1341 -- reduction rate
1342 -- x_change_in_cost_to_reduce : Reduction amount of changed cost for a year
1343 -- x_total_change_in_cost : Total changed cost for a year
1344 -- x_net_proceeds : 'Proceeds - Cost of Removal' for a year
1345 -- with only applying reduction rate.
1346 -- x_net_proceeds_to_reduce : Reduction amount of
1347 -- 'Proceeds - Cost of Removal' for a year
1348 -- x_total_net_proceeds : Total of 'Proceeds - Cost of Removal'
1349 -- for a year
1350 -- x_first_half_cost : Changed cost of 1st half year
1351 -- with applying reduction amount
1352 -- x_first_half_cost_to_reduce : Reduction amount of changed cost of
1353 -- 1st half year.
1354 -- x_second_half_cost : Changed cost of 2nd half year
1355 -- with applying reduction amount
1356 -- x_second_half_cost_to_reduce : Reduction amount of changed cost of
1357 -- 2nd half year
1358 -------------------------------------------------------------------------------
1359 FUNCTION CALC_REDUCTION_AMOUNT
1360 (
1361 p_asset_id IN NUMBER,
1362 p_group_asset_id IN NUMBER,
1363 p_asset_type IN VARCHAR2,
1364 p_book_type_code IN VARCHAR2,
1365 p_period_counter IN NUMBER,
1366 p_transaction_date IN DATE,
1367 p_half_year_rule_flag IN VARCHAR2,
1368 p_mrc_sob_type_code IN VARCHAR2,
1369 x_change_in_cost OUT NOCOPY NUMBER,
1370 x_change_in_cost_to_reduce OUT NOCOPY NUMBER,
1371 x_total_change_in_cost OUT NOCOPY NUMBER,
1372 x_net_proceeds OUT NOCOPY NUMBER,
1373 x_net_proceeds_to_reduce OUT NOCOPY NUMBER,
1374 x_total_net_proceeds OUT NOCOPY NUMBER,
1375 x_first_half_cost OUT NOCOPY NUMBER,
1376 x_first_half_cost_to_reduce OUT NOCOPY NUMBER,
1377 x_second_half_cost OUT NOCOPY NUMBER,
1378 x_second_half_cost_to_reduce OUT NOCOPY NUMBER,
1379 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1380 return boolean is
1381
1382 -- Query start date and end date of fiscal year and period
1383 cursor C_GET_DATE is
1384 select fy.start_date fy_start_date,
1385 fy.end_date fy_end_date,
1386 fy.mid_year_date fy_mid_year_date,
1387 dp.calendar_period_open_date cp_start_date,
1388 dp.calendar_period_close_date cp_end_date
1389 from FA_FISCAL_YEAR fy,
1390 FA_DEPRN_PERIODS dp,
1391 FA_BOOK_CONTROLS bc
1392 where bc.book_type_code = dp.book_type_code
1393 and fy.fiscal_year = dp.fiscal_year
1394 and bc.fiscal_year_name = fy.fiscal_year_name
1395 and dp.book_type_code= p_book_type_code
1396 and dp.period_counter = p_period_counter;
1397
1398 -- Query start date and end date of fiscal year and period for MRC
1399 cursor C_GET_DATE_M is
1400 select fy.start_date fy_start_date,
1401 fy.end_date fy_end_date,
1402 fy.mid_year_date fy_mid_year_date,
1403 dp.calendar_period_open_date cp_start_date,
1404 dp.calendar_period_close_date cp_end_date
1405 from FA_FISCAL_YEAR fy,
1406 FA_DEPRN_PERIODS_MRC_V dp,
1407 FA_BOOK_CONTROLS_MRC_V bc
1408 where bc.book_type_code = dp.book_type_code
1409 and fy.fiscal_year = dp.fiscal_year
1410 and bc.fiscal_year_name = fy.fiscal_year_name
1411 and dp.book_type_code= p_book_type_code
1412 and dp.period_counter = p_period_counter;
1413
1414 ----------------------------
1415 -- For Non MRC
1416 ---------------------------
1417
1418 ----------------------------------------------------------------------
1419 -- For member assets and standalone asset
1420 ----------------------------------------------------------------------
1421
1422 -- Query changed cost and proceeds wiht applying reduction rate during a year or half_year
1423
1424 cursor C_REDUCE_COST_AMOUNT (t_start_date date,
1425 t_end_date date)
1426 is
1427 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) change_in_cost,
1428 sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0))
1429 change_in_cost_to_reduce,
1430 sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1431 sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
1432 from FA_BOOKS BK1,
1433 FA_BOOKS BK2,
1434 FA_RETIREMENTS RET,
1435 FA_TRANSACTION_HEADERS TH
1436 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1437 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1438 and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1439 and BK2.ASSET_ID= p_asset_id
1440 and BK2.BOOK_TYPE_CODE = p_book_type_code
1441 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1442 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1443 and nvl(BK2.REDUCTION_RATE,0) >0;
1444
1445 -- Query all changed costs and proceeds during a year
1446
1447 cursor C_FY_TOTAL_COST_AMOUNT (t_start_date date,
1448 t_end_date date)
1449 is
1450 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1451 sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1452 from FA_BOOKS BK1,
1453 FA_BOOKS BK2,
1454 FA_RETIREMENTS RET,
1455 FA_TRANSACTION_HEADERS TH
1456 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1457 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1458 and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1459 and BK2.ASSET_ID= p_asset_id
1460 and BK2.BOOK_TYPE_CODE = p_book_type_code
1461 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1462 and TH.TRANSACTION_DATE_ENTERED <= t_end_date;
1463
1464 ----------------------------------------------------------------------------
1465 -- For Group Assets
1466 ----------------------------------------------------------------------------
1467
1468 -- Query changed cost and proceeds wiht applying reduction rate during a year or half_year
1469
1470 cursor GP_REDUCE_COST_AMOUNT (t_start_date date,
1471 t_end_date date)
1472 is
1473 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) change_in_cost,
1474 sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0))
1475 change_in_cost_to_reduce,
1476 sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1477 sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
1478 from FA_BOOKS BK1,
1479 FA_BOOKS BK2,
1480 FA_BOOKS BK3,
1481 FA_RETIREMENTS RET,
1482 FA_TRANSACTION_HEADERS TH
1483 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1484 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1485 and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1486 and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1487 and BK2.ASSET_ID= p_asset_id
1488 and BK2.BOOK_TYPE_CODE = p_book_type_code
1489 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1490 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1491 and nvl(BK3.REDUCTION_RATE,0) >0
1492 and exists (select BK3.ASSET_ID
1493 from FA_BOOKS BK4
1494 where BK3.ASSET_ID = BK4.ASSET_ID and
1495 BK4.BOOK_TYPE_CODE = p_book_type_code and
1496 BK4.GROUP_ASSET_ID = p_asset_id and
1497 BK4. DATE_INEFFECTIVE is null);
1498
1499 -- Query all changed costs and proceeds during a year
1500
1501 cursor GP_FY_TOTAL_COST_AMOUNT (t_start_date date,
1502 t_end_date date)
1503 is
1504 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1505 sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1506 from FA_BOOKS BK1,
1507 FA_BOOKS BK2,
1508 FA_BOOKS BK3,
1509 FA_RETIREMENTS RET,
1510 FA_TRANSACTION_HEADERS TH
1511 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1512 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1513 and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1514 and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1515 and BK2.ASSET_ID= p_asset_id
1516 and BK2.BOOK_TYPE_CODE = p_book_type_code
1517 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1518 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1519 and exists (select BK3.ASSET_ID
1520 from FA_BOOKS BK4
1521 where BK3.ASSET_ID = BK4.ASSET_ID and
1522 BK4.BOOK_TYPE_CODE = p_book_type_code and
1523 BK4.GROUP_ASSET_ID = p_asset_id and
1524 BK4. DATE_INEFFECTIVE is null);
1525
1526 --------------------------------------------------------------
1527 -- For MRC
1528 --------------------------------------------------------------
1529
1530 ----------------------------------------------------------------------
1531 -- For member assets and standalone asset
1532 ----------------------------------------------------------------------
1533
1534 -- Query changed cost and proceeds wiht applying reduction rate during a year or half_year
1535
1536 cursor C_REDUCE_COST_AMOUNT_M (t_start_date date,
1537 t_end_date date)
1538 is
1539 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) change_in_cost,
1540 sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0))
1541 change_in_cost_to_reduce,
1542 sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1543 sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
1544 from FA_BOOKS_MRC_V BK1,
1545 FA_BOOKS_MRC_V BK2,
1546 FA_RETIREMENTS_MRC_V RET,
1547 FA_TRANSACTION_HEADERS TH
1548 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1549 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1550 and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1551 and BK2.ASSET_ID= p_asset_id
1552 and BK2.BOOK_TYPE_CODE = p_book_type_code
1553 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1554 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1555 and nvl(BK2.REDUCTION_RATE,0) >0;
1556
1557 -- Query all changed costs and proceeds during a year
1558
1559 cursor C_FY_TOTAL_COST_AMOUNT_M (t_start_date date,
1560 t_end_date date)
1561 is
1562 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1563 sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1564 from FA_BOOKS_MRC_V BK1,
1565 FA_BOOKS_MRC_V BK2,
1566 FA_RETIREMENTS_MRC_V RET,
1567 FA_TRANSACTION_HEADERS TH
1568 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1569 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1570 and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1571 and BK2.ASSET_ID= p_asset_id
1572 and BK2.BOOK_TYPE_CODE = p_book_type_code
1573 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1574 and TH.TRANSACTION_DATE_ENTERED <= t_end_date;
1575
1576 ----------------------------------------------------------------------------
1577 -- For Group Assets
1578 ----------------------------------------------------------------------------
1579
1580 -- Query changed cost and proceeds wiht applying reduction rate during a year or half_year
1581
1582 cursor GP_REDUCE_COST_AMOUNT_M (t_start_date date,
1583 t_end_date date)
1584 is
1585 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) change_in_cost,
1586 sum((BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0))
1587 change_in_cost_to_reduce,
1588 sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1589 sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
1590 from FA_BOOKS_MRC_V BK1,
1591 FA_BOOKS_MRC_V BK2,
1592 FA_BOOKS_MRC_V BK3,
1593 FA_RETIREMENTS_MRC_V RET,
1594 FA_TRANSACTION_HEADERS TH
1595 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1596 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1597 and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1598 and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1599 and BK2.ASSET_ID= p_asset_id
1600 and BK2.BOOK_TYPE_CODE = p_book_type_code
1601 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1602 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1603 and nvl(BK3.REDUCTION_RATE,0) >0
1604 and exists (select BK3.ASSET_ID
1605 from FA_BOOKS_MRC_V BK4
1606 where BK3.ASSET_ID = BK4.ASSET_ID and
1607 BK4.BOOK_TYPE_CODE = p_book_type_code and
1608 BK4.GROUP_ASSET_ID = p_asset_id and
1609 BK4. DATE_INEFFECTIVE is null);
1610
1611 -- Query all changed costs and proceeds during a year
1612
1613 cursor GP_FY_TOTAL_COST_AMOUNT_M (t_start_date date,
1614 t_end_date date)
1615 is
1616 select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1617 sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1618 from FA_BOOKS_MRC_V BK1,
1619 FA_BOOKS_MRC_V BK2,
1620 FA_BOOKS_MRC_V BK3,
1621 FA_RETIREMENTS_MRC_V RET,
1622 FA_TRANSACTION_HEADERS TH
1623 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1624 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1625 and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1626 and TH.MEMBER_TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1627 and BK2.ASSET_ID= p_asset_id
1628 and BK2.BOOK_TYPE_CODE = p_book_type_code
1629 and TH.TRANSACTION_DATE_ENTERED >= t_start_date
1630 and TH.TRANSACTION_DATE_ENTERED <= t_end_date
1631 and exists (select BK3.ASSET_ID
1632 from FA_BOOKS_MRC_V BK4
1633 where BK3.ASSET_ID = BK4.ASSET_ID and
1634 BK4.BOOK_TYPE_CODE = p_book_type_code and
1635 BK4.GROUP_ASSET_ID = p_asset_id and
1636 BK4. DATE_INEFFECTIVE is null);
1637
1638 -- Query member's reduction rate in this transaction
1639 cursor C_REDUCTION_RATE (l_transaction_header_id number)
1640 is
1641 select nvl(REDUCTION_RATE,0)
1642 from FA_BOOKS
1643 where TRANSACTION_HEADER_ID_IN = l_transaction_header_id;
1644
1645 -- For MRC
1646 cursor C_REDUCTION_RATE_M (l_transaction_header_id number)
1647 is
1648 select nvl(REDUCTION_RATE,0)
1649 from FA_BOOKS_MRC_V
1650 where TRANSACTION_HEADER_ID_IN = l_transaction_header_id;
1651
1652 ------------------------------
1653 -- Check transaction existing
1654 ------------------------------
1655 cursor C_CHK_TRANSACTION (l_transaction_header_id number)
1656 is
1657 select count('Y')
1658 from FA_TRANSACTION_HEADERS TH
1659 where TH.TRANSACTION_HEADER_ID = l_transaction_header_id
1660 and TH.ASSET_ID = p_asset_id
1661 and TH.BOOK_TYPE_CODE = p_book_type_code;
1662
1663 --
1664 l_fy_start_date date; -- Fiscal year start date
1665 l_fy_end_date date; -- Fiscal year end date
1666 l_fy_mid_year_date date; -- Fiscal year mid year date
1667 l_cp_start_date date; -- Period start date
1668 l_cp_end_date date; -- Period end date
1669 l_first_end_date date; -- First half year end date
1670 l_transaction_date date; -- Processing Transaction Date
1671
1672 l_change_in_cost NUMBER := NULL;
1673 l_change_in_cost_to_reduce NUMBER := NULL;
1674 l_total_change_in_cost NUMBER := NULL;
1675 l_net_proceeds NUMBER := NULL;
1676 l_net_proceeds_to_reduce NUMBER := NULL;
1677 l_total_net_proceeds NUMBER := NULL;
1678 l_first_half_cost NUMBER := NULL;
1679 l_first_half_cost_to_reduce NUMBER := NULL;
1680 l_second_half_cost NUMBER := NULL;
1681 l_second_half_cost_to_reduce NUMBER := NULL;
1682
1683 l_dummy_proceeds NUMBER; -- Dummy
1684 l_dummy_to_reduce NUMBER; -- Dummy
1685 l_chk_count NUMBER := NULL;
1686
1687 l_group_cost_accounting_flag VARCHAR2(1);
1688
1689 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis1_pkg.CALC_REDUCITON_AMOUNT';
1690
1691 no_mid_date_err exception;
1692 calc_reduction_amount_err exception;
1693
1694 begin
1695
1696 if (p_mrc_sob_type_code <> 'R') then -- Not MRC
1697
1698 -- Get Fiscal Year and Period start and End date
1699
1700 OPEN C_GET_DATE;
1701 Fetch C_GET_DATE into l_fy_start_date,l_fy_end_date, l_fy_mid_year_date,
1702 l_cp_start_date,l_cp_end_date;
1703 if(C_GET_DATE%NOTFOUND)then
1704 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar) then
1705 raise no_mid_date_err;
1706 end if;
1707 --bug fix 5005592
1708 begin
1709 SELECT cp.start_date,
1710 cp.end_date,
1711 fy.start_date,
1712 fy.end_date,
1713 fy.mid_year_date
1714 INTO l_cp_start_date,
1715 l_cp_end_date,
1716 l_fy_start_date,
1717 l_fy_end_date,
1718 l_fy_mid_year_date
1719 FROM fa_calendar_periods cp,
1720 fa_fiscal_year fy,
1721 fa_calendar_types cal_ty
1722 WHERE fy.fiscal_year = floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)
1723 AND fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
1724 AND cal_ty.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
1725 AND cp.calendar_type = cal_ty.calendar_type
1726 AND cp.start_date BETWEEN fy.start_date AND fy.end_date
1727 AND cp.end_date BETWEEN fy.start_date AND fy.end_date
1728 and period_num = mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR);
1729 exception
1730 when others then
1731 raise no_mid_date_err;
1732 end;
1733 end if;
1734 --bug fix ends 5005592
1735
1736
1737 Close C_GET_DATE;
1738
1739 if p_log_level_rec.statement_level then
1740 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1741 element=>'l_fy_start_date(1)',
1742 value=> l_fy_start_date);
1743 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1744 element=>'l_fy_end_date(1)',
1745 value=> l_fy_end_date);
1746 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1747 element=>'l_fy_mid_year_date(1)',
1748 value=> l_fy_mid_year_date);
1749 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1750 element=>'l_cp_start_date(1)',
1751 value=> l_cp_start_date);
1752 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1753 element=>'l_cp_end_date(1)',
1754 value=> l_cp_end_date);
1755 end if;
1756
1757 -- If user will use half year rule and doesn't setup mid year date,
1758 -- the error is raised.
1759
1760 If p_half_year_rule_flag='Y' and l_fy_mid_year_date is null then
1761 raise no_mid_date_err;
1762 end if;
1763
1764 -- Set transaction Date
1765 if p_transaction_date is not null then
1766 l_transaction_date:= p_transaction_date;
1767 else
1768 l_transaction_date:= l_cp_end_date;
1769 end if;
1770
1771 if p_log_level_rec.statement_level then
1772 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1773 element=>'l_transaction_date(1)',
1774 value=> l_transaction_date);
1775 end if;
1776
1777 -- Group asset
1778 if p_asset_type ='GROUP' then
1779
1780 -- Get the changed cost and proceeds with applying reduction rate
1781 -- and those reduction amounts for a year
1782
1783 open GP_REDUCE_COST_AMOUNT(l_fy_start_date,l_transaction_date);
1784 fetch GP_REDUCE_COST_AMOUNT into
1785 l_change_in_cost,l_change_in_cost_to_reduce,
1786 l_net_proceeds, l_net_proceeds_to_reduce;
1787 close GP_REDUCE_COST_AMOUNT;
1788
1789 -- Get the all changed cost and proceeds for a year
1790
1791 open GP_FY_TOTAL_COST_AMOUNT(l_fy_start_date,l_transaction_date);
1792 fetch GP_FY_TOTAL_COST_AMOUNT
1793 into l_total_change_in_cost,l_total_net_proceeds;
1794 close GP_FY_TOTAL_COST_AMOUNT;
1795
1796 if p_log_level_rec.statement_level then
1797 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1798 element=>'l_change_in_cost(1)',
1799 value=> l_change_in_cost);
1800 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1801 element=>'l_change_in_cost_to_reduce(1)',
1802 value=> l_change_in_cost_to_reduce);
1803 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1804 element=>'l_total_change_in_cost(1)',
1805 value=> l_total_change_in_cost);
1806 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1807 element=>'l_net_proceeds(1)',
1808 value=> l_net_proceeds);
1809 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1810 element=>'l_net_proceeds_to_reduce(1)',
1811 value=> l_net_proceeds_to_reduce);
1812 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1813 element=>'l_total_net_proceeds(1)',
1814 value=> l_total_net_proceeds);
1815 end if;
1816
1817 else
1818 -- Individual assets
1819 -- member assets
1820
1821 -- Get the changed cost and proceeds with applying reduction rate
1822 -- and those reduction amounts for a year
1823
1824 open C_REDUCE_COST_AMOUNT(l_fy_start_date,l_transaction_date);
1825 fetch C_REDUCE_COST_AMOUNT into
1826 l_change_in_cost,l_change_in_cost_to_reduce,
1827 l_net_proceeds, l_net_proceeds_to_reduce;
1828 close C_REDUCE_COST_AMOUNT;
1829
1830 -- Get the all changed cost and proceeds for a year
1831
1832 open C_FY_TOTAL_COST_AMOUNT(l_fy_start_date,l_transaction_date);
1833 fetch C_FY_TOTAL_COST_AMOUNT
1834 into l_total_change_in_cost,l_total_net_proceeds;
1835 close C_FY_TOTAL_COST_AMOUNT;
1836
1837 if p_log_level_rec.statement_level then
1838 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1839 element=>'l_change_in_cost(2)',
1840 value=> l_change_in_cost);
1841 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1842 element=>'l_change_in_cost_to_reduce(2)',
1843 value=> l_change_in_cost_to_reduce);
1844 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1845 element=>'l_total_change_in_cost(2)',
1846 value=> l_total_change_in_cost);
1847 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1848 element=>'l_net_proceeds(2)',
1849 value=> l_net_proceeds);
1850 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1851 element=>'l_net_proceeds_to_reduce(2)',
1852 value=> l_net_proceeds_to_reduce);
1853 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1854 element=>'l_total_net_proceeds(2)',
1855 value=> l_total_net_proceeds);
1856 end if;
1857
1858 end if; -- End member/group and standalone assets condition
1859
1860
1861 else -- MRC
1862
1863 -- Get Fiscal Year and Period start and End date
1864
1865 OPEN C_GET_DATE_M;
1866 Fetch C_GET_DATE_M into l_fy_start_date,l_fy_end_date, l_fy_mid_year_date,
1867 l_cp_start_date,l_cp_end_date;
1868 if(C_GET_DATE_M%NOTFOUND)then
1869 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar) then
1870 raise no_mid_date_err;
1871 end if;
1872 --bug fix 5005592
1873 begin
1874 SELECT cp.start_date,
1875 cp.end_date,
1876 fy.start_date,
1877 fy.end_date,
1878 fy.mid_year_date
1879 INTO l_cp_start_date,
1880 l_cp_end_date,
1881 l_fy_start_date,
1882 l_fy_end_date,
1883 l_fy_mid_year_date
1884 FROM fa_calendar_periods cp,
1885 fa_fiscal_year fy,
1886 fa_calendar_types cal_ty
1887 WHERE fy.fiscal_year = floor(p_period_counter/cal_ty.NUMBER_PER_FISCAL_YEAR)
1888 AND fy.fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
1889 AND cal_ty.calendar_type = fa_cache_pkg.fazcbc_record.deprn_calendar
1890 AND cp.calendar_type = cal_ty.calendar_type
1891 AND cp.start_date BETWEEN fy.start_date AND fy.end_date
1892 AND cp.end_date BETWEEN fy.start_date AND fy.end_date
1893 and period_num = mod(p_period_counter,cal_ty.NUMBER_PER_FISCAL_YEAR);
1894 exception
1895 when others then
1896 raise no_mid_date_err;
1897 end;
1898 end if;
1899 --bug fix 5005592 ends
1900
1901 Close C_GET_DATE_M;
1902
1903 if p_log_level_rec.statement_level then
1904 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1905 element=>'l_fy_start_date(2)',
1906 value=> l_fy_start_date);
1907 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1908 element=>'l_fy_end_date(2)',
1909 value=> l_fy_end_date);
1910 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1911 element=>'l_fy_mid_year_date(2)',
1912 value=> l_fy_mid_year_date);
1913 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1914 element=>'l_cp_start_date(2)',
1915 value=> l_cp_start_date);
1916 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1917 element=>'l_cp_end_date(2)',
1918 value=> l_cp_end_date);
1919 end if;
1920
1921 -- If user will use half year rule and doesn't setup mid year date,
1922 -- the error is raised.
1923
1924 If p_half_year_rule_flag='Y' and l_fy_mid_year_date is null then
1925 raise no_mid_date_err;
1926 end if;
1927
1928 -- Set transaction Date
1929 if p_transaction_date is not null then
1930 l_transaction_date:= p_transaction_date;
1931 else
1932 l_transaction_date:= l_cp_end_date;
1933 end if;
1934
1935 if p_log_level_rec.statement_level then
1936 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1937 element=>'l_transaction_date(2)',
1938 value=> l_transaction_date);
1939 end if;
1940
1941 -- Group asset
1942 if p_asset_type ='GROUP' then
1943
1944 -- Get the changed cost and proceeds with applying reduction rate
1945 -- and those reduction amounts for a year
1946
1947 open GP_REDUCE_COST_AMOUNT_M(l_fy_start_date,l_transaction_date);
1948 fetch GP_REDUCE_COST_AMOUNT_M into
1949 l_change_in_cost,l_change_in_cost_to_reduce,
1950 l_net_proceeds, l_net_proceeds_to_reduce;
1951 close GP_REDUCE_COST_AMOUNT_M;
1952
1953 -- Get the all changed cost and proceeds for a year
1954
1955 open GP_FY_TOTAL_COST_AMOUNT_M(l_fy_start_date,l_transaction_date);
1956 fetch GP_FY_TOTAL_COST_AMOUNT_M
1957 into l_total_change_in_cost,l_total_net_proceeds;
1958 close GP_FY_TOTAL_COST_AMOUNT_M;
1959
1960 if p_log_level_rec.statement_level then
1961 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1962 element=>'l_change_in_cost(3)',
1963 value=> l_change_in_cost);
1964 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1965 element=>'l_change_in_cost_to_reduce(3)',
1966 value=> l_change_in_cost_to_reduce);
1967 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1968 element=>'l_total_change_in_cost(3)',
1969 value=> l_total_change_in_cost);
1970 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1971 element=>'l_net_proceeds(3)',
1972 value=> l_net_proceeds);
1973 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1974 element=>'l_net_proceeds_to_reduce(3)',
1975 value=> l_net_proceeds_to_reduce);
1976 fa_debug_pkg.add(fname=>'calc_reduction_amount',
1977 element=>'l_total_net_proceeds(3)',
1978 value=> l_total_net_proceeds);
1979 end if;
1980
1981 else
1982 -- Individual assets
1983 -- member assets
1984
1985 -- Get the changed cost and proceeds with applying reduction rate
1986 -- and those reduction amounts for a year
1987
1988 open C_REDUCE_COST_AMOUNT_M(l_fy_start_date,l_transaction_date);
1989 fetch C_REDUCE_COST_AMOUNT_M into
1990 l_change_in_cost,l_change_in_cost_to_reduce,
1991 l_net_proceeds, l_net_proceeds_to_reduce;
1992 close C_REDUCE_COST_AMOUNT_M;
1993
1994 -- Get the all changed cost and proceeds for a year
1995
1996 open C_FY_TOTAL_COST_AMOUNT_M(l_fy_start_date,l_transaction_date);
1997 fetch C_FY_TOTAL_COST_AMOUNT_M
1998 into l_total_change_in_cost,l_total_net_proceeds;
1999 close C_FY_TOTAL_COST_AMOUNT_M;
2000
2001 if p_log_level_rec.statement_level then
2002 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2003 element=>'l_change_in_cost(4)',
2004 value=> l_change_in_cost);
2005 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2006 element=>'l_change_in_cost_to_reduce(4)',
2007 value=> l_change_in_cost_to_reduce);
2008 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2009 element=>'l_total_change_in_cost(4)',
2010 value=> l_total_change_in_cost);
2011 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2012 element=>'l_net_proceeds(4)',
2013 value=> l_net_proceeds);
2014 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2015 element=>'l_net_proceeds_to_reduce(4)',
2016 value=> l_net_proceeds_to_reduce);
2017 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2018 element=>'l_total_net_proceeds(4)',
2019 value=> l_total_net_proceeds);
2020 end if;
2021
2022 end if; -- End member/group and standalone assets condition
2023
2024 end if; -- End of MRC
2025
2026 -- If Half year rule is applied, get the amounts of 1st and 2nd half year
2027 If p_half_year_rule_flag='Y' then
2028
2029 -- Set First half year's end date for query and set 2nd half year's amount
2030 If l_transaction_date < l_fy_mid_year_date then -- set first half end date
2031 l_first_end_date := l_transaction_date;
2032
2033 -- Set 2nd half year's amount to 0
2034 l_second_half_cost := 0;
2035 l_second_half_cost_to_reduce := 0;
2036 else
2037 l_first_end_date := l_fy_mid_year_date - 1;
2038
2039 if (p_mrc_sob_type_code <> 'R') then -- Not MRC
2040
2041 if p_asset_type ='GROUP' then
2042
2043 --Get 2nd half year's amount.
2044 open GP_REDUCE_COST_AMOUNT (l_fy_mid_year_date,l_transaction_date);
2045 fetch GP_REDUCE_COST_AMOUNT
2046 into l_second_half_cost,l_second_half_cost_to_reduce,l_dummy_proceeds,l_dummy_to_reduce;
2047 close GP_REDUCE_COST_AMOUNT;
2048
2049 if p_log_level_rec.statement_level then
2050 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2051 element=>'l_second_half_cost(1)',
2052 value=> l_second_half_cost);
2053 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2054 element=>'l_second_half_cost_to_reduce(1)',
2055 value=> l_second_half_cost_to_reduce);
2056 end if;
2057
2058 else
2059 -- Individual assets
2060 -- member assets
2061
2062 --Get 2nd half year's amount.
2063 open C_REDUCE_COST_AMOUNT (l_fy_mid_year_date,l_transaction_date);
2064 fetch C_REDUCE_COST_AMOUNT
2065 into l_second_half_cost,l_second_half_cost_to_reduce,l_dummy_proceeds,l_dummy_to_reduce;
2066 close C_REDUCE_COST_AMOUNT;
2067
2068 if p_log_level_rec.statement_level then
2069 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2070 element=>'l_second_half_cost(2)',
2071 value=> l_second_half_cost);
2072 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2073 element=>'l_second_half_cost_to_reduce(2)',
2074 value=> l_second_half_cost_to_reduce);
2075 end if;
2076
2077 end if;
2078
2079 else -- MRC
2080
2081 -- Group asset
2082 if p_asset_type ='GROUP' then
2083
2084 --Get 2nd half year's amount.
2085 open GP_REDUCE_COST_AMOUNT (l_fy_mid_year_date,l_transaction_date);
2086 fetch GP_REDUCE_COST_AMOUNT_M
2087 into l_second_half_cost,l_second_half_cost_to_reduce,l_dummy_proceeds,l_dummy_to_reduce;
2088 close GP_REDUCE_COST_AMOUNT_M;
2089
2090 if p_log_level_rec.statement_level then
2091 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2092 element=>'l_second_half_cost(3)',
2093 value=> l_second_half_cost);
2094 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2095 element=>'l_second_half_cost_to_reduce(3)',
2096 value=> l_second_half_cost_to_reduce);
2097 end if;
2098
2099 else
2100 -- Individual assets
2101 -- member assets
2102
2103 --Get 2nd half year's amount.
2104 open C_REDUCE_COST_AMOUNT_M (l_fy_mid_year_date,l_transaction_date);
2105 fetch C_REDUCE_COST_AMOUNT_M
2106 into l_second_half_cost,l_second_half_cost_to_reduce,l_dummy_proceeds,l_dummy_to_reduce;
2107 close C_REDUCE_COST_AMOUNT_M;
2108
2109 if p_log_level_rec.statement_level then
2110 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2111 element=>'l_second_half_cost(4)',
2112 value=> l_second_half_cost);
2113 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2114 element=>'l_second_half_cost_to_reduce(4)',
2115 value=> l_second_half_cost_to_reduce);
2116 end if;
2117
2118 end if; -- End member/group and standalone assets condition
2119
2120 end if; --End of MRC
2121
2122 end if; -- End set first half end date
2123
2124 -- Set 1st half year amount
2125 if (p_mrc_sob_type_code <> 'R') then -- Not MRC
2126
2127 -- Group assets
2128 if p_asset_type ='GROUP' then
2129
2130 -- Get changed cost and reduction amount during 1st half year
2131 open GP_REDUCE_COST_AMOUNT (l_fy_start_date, l_first_end_date);
2132 fetch GP_REDUCE_COST_AMOUNT into l_first_half_cost, l_first_half_cost_to_reduce,
2133 l_dummy_proceeds,l_dummy_to_reduce;
2134 close GP_REDUCE_COST_AMOUNT;
2135
2136 if p_log_level_rec.statement_level then
2137 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2138 element=>'l_first_half_cost(1)',
2139 value=> l_first_half_cost);
2140 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2141 element=>'l_first_half_cost_to_reduce(1)',
2142 value=> l_first_half_cost_to_reduce);
2143 end if;
2144
2145 else
2146 -- Individual assets
2147 -- member assets
2148
2149 -- Get changed cost and reduction amount during 1st half year
2150 open C_REDUCE_COST_AMOUNT (l_fy_start_date, l_first_end_date);
2151 fetch C_REDUCE_COST_AMOUNT into l_first_half_cost, l_first_half_cost_to_reduce,
2152 l_dummy_proceeds,l_dummy_to_reduce;
2153 close C_REDUCE_COST_AMOUNT;
2154
2155 if p_log_level_rec.statement_level then
2156 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2157 element=>'l_first_half_cost(2)',
2158 value=> l_first_half_cost);
2159 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2160 element=>'l_first_half_cost_to_reduce(2)',
2161 value=> l_first_half_cost_to_reduce);
2162 end if;
2163
2164 end if;
2165
2166 else -- MRC
2167
2168 -- Group assets
2169 if l_group_cost_accounting_flag <>'Y'
2170 and p_asset_type ='GROUP' then
2171
2172 -- Get changed cost and reduction amount during 1st half year
2173 open GP_REDUCE_COST_AMOUNT_M (l_fy_start_date, l_first_end_date);
2174 fetch GP_REDUCE_COST_AMOUNT_M into l_first_half_cost, l_first_half_cost_to_reduce,
2175 l_dummy_proceeds,l_dummy_to_reduce;
2176 close GP_REDUCE_COST_AMOUNT_M;
2177
2178 if p_log_level_rec.statement_level then
2179 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2180 element=>'l_first_half_cost(3)',
2181 value=> l_first_half_cost);
2182 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2183 element=>'l_first_half_cost_to_reduce(3)',
2184 value=> l_first_half_cost_to_reduce);
2185 end if;
2186
2187 else -- For member and standalone assets
2188
2189 -- Get changed cost and reduction amount during 1st half year
2190 open C_REDUCE_COST_AMOUNT_M (l_fy_start_date, l_first_end_date);
2191 fetch C_REDUCE_COST_AMOUNT_M into l_first_half_cost, l_first_half_cost_to_reduce,
2192 l_dummy_proceeds,l_dummy_to_reduce;
2193 close C_REDUCE_COST_AMOUNT_M;
2194
2195 if p_log_level_rec.statement_level then
2196 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2197 element=>'l_first_half_cost(4)',
2198 value=> l_first_half_cost);
2199 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2200 element=>'l_first_half_cost_to_reduce(4)',
2201 value=> l_first_half_cost_to_reduce);
2202 end if;
2203
2204 end if;
2205
2206 end if; -- End of MRC
2207
2208 end if; -- End Half year rule is applied
2209
2210 ------------------------------------------------------
2211 -- Add this transaction's amount to queried variables
2212 -- for Group asset
2213 ------------------------------------------------------
2214 if fa_calc_deprn_basis1_pkg.g_rule_in.transaction_date_entered >= l_fy_start_date
2215 and fa_calc_deprn_basis1_pkg.g_rule_in.transaction_date_entered <= nvl(l_transaction_date,l_fy_end_date)
2216 and not (fa_calc_deprn_basis1_pkg.g_rule_in.asset_type <>'GROUP'
2217 and fa_calc_deprn_basis1_pkg.g_rule_in.tracking_method='ALLOCATE')
2218 then
2219
2220 -- Check whether this transaction is existing or not.
2221 OPEN C_CHK_TRANSACTION (fa_calc_deprn_basis1_pkg.g_rule_in.transaction_header_id);
2222 FETCH C_CHK_TRANSACTION into l_chk_count;
2223 CLOSE C_CHK_TRANSACTION;
2224
2225 if nvl(l_chk_count,0) = 0 then
2226
2227 -- Get member entered reduction rate.
2228 if p_asset_type ='GROUP' then
2229 if (p_mrc_sob_type_code <> 'R') then
2230 OPEN C_REDUCTION_RATE(g_rule_in.member_transaction_header_id);
2231 FETCH C_REDUCTION_RATE into g_rule_in.reduction_rate;
2232 CLOSE C_REDUCTION_RATE;
2233 else -- Not MRC
2234 OPEN C_REDUCTION_RATE_M(g_rule_in.member_transaction_header_id);
2235 FETCH C_REDUCTION_RATE_M into g_rule_in.reduction_rate;
2236 CLOSE C_REDUCTION_RATE_M;
2237 end if; -- End of MRC
2238
2239 if p_log_level_rec.statement_level then
2240 fa_debug_pkg.add(fname=>'calc_reduction_amount',
2241 element=>'updated reduction_rate',
2242 value=> fa_calc_deprn_basis1_pkg.g_rule_in.reduction_rate
2243 ,p_log_level_rec => p_log_level_rec);
2244 end if;
2245 end if; -- End of Group
2246
2247 if g_rule_in.event_type='RETIREMENT' then
2248 x_total_change_in_cost := nvl(l_total_change_in_cost,0);
2249 x_total_net_proceeds
2250 := nvl(l_total_net_proceeds,0)
2251 + nvl(g_rule_in.nbv_retired,0);
2252 elsif g_rule_in.event_type ='AMORT_ADJ'
2253 and nvl(g_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
2254 and g_rule_in.asset_type ='GROUP'
2255 then
2256 x_total_change_in_cost := nvl(l_total_change_in_cost,0);
2257 x_total_net_proceeds
2258 := nvl(l_total_net_proceeds,0) + nvl(g_rule_in.member_proceeds,0);
2259 else
2260 x_total_change_in_cost
2261 := nvl(l_total_change_in_cost,0) + nvl(g_rule_in.adjustment_amount,0);
2262 x_total_net_proceeds:= nvl(l_total_net_proceeds,0);
2263 end if;
2264
2265 if nvl(g_rule_in.reduction_rate,0) <> 0 then
2266
2267 if g_rule_in.event_type='RETIREMENT' then
2268 x_change_in_cost := nvl(l_change_in_cost,0);
2269 x_change_in_cost_to_reduce := nvl(l_change_in_cost_to_reduce,0);
2270 x_net_proceeds
2271 := nvl(l_net_proceeds,0)
2272 + nvl(g_rule_in.nbv_retired,0);
2273 x_net_proceeds_to_reduce
2274 := nvl(l_net_proceeds_to_reduce,0)
2275 + nvl(g_rule_in.nbv_retired,0) *nvl(g_rule_in.reduction_rate,0);
2276
2277 elsif g_rule_in.event_type ='AMORT_ADJ'
2278 and nvl(g_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
2279 and g_rule_in.asset_type ='GROUP'
2280 then
2281 x_change_in_cost := nvl(l_change_in_cost,0);
2282 x_change_in_cost_to_reduce := nvl(l_change_in_cost_to_reduce,0);
2283 x_net_proceeds
2284 := nvl(l_net_proceeds,0) + nvl(g_rule_in.member_proceeds,0);
2285 x_net_proceeds_to_reduce
2286 := nvl(l_net_proceeds_to_reduce,0)
2287 + nvl(g_rule_in.member_proceeds,0) *nvl(g_rule_in.reduction_rate,0);
2288
2289 else
2290
2291 x_change_in_cost
2292 := nvl(l_change_in_cost,0) + nvl(g_rule_in.adjustment_amount,0);
2293 x_change_in_cost_to_reduce
2294 := nvl(l_change_in_cost_to_reduce,0) + nvl(g_rule_in.adjustment_amount,0)*nvl(g_rule_in.reduction_rate,0);
2295 x_net_proceeds := nvl(l_net_proceeds,0);
2296 x_net_proceeds_to_reduce := nvl(l_net_proceeds_to_reduce,0);
2297 end if; -- event type
2298
2299 if g_rule_in.transaction_date_entered < l_fy_mid_year_date
2300 then -- First half year
2301
2302 if g_rule_in.event_type ='RETIREMENT'
2303 or (g_rule_in.event_type ='AMORT_ADJ'
2304 and nvl(g_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
2305 and g_rule_in.asset_type ='GROUP')
2306 then
2307 x_first_half_cost := nvl(l_first_half_cost,0) ;
2308 x_first_half_cost_to_reduce := nvl(l_first_half_cost_to_reduce,0);
2309 else
2310 x_first_half_cost
2311 := nvl(l_first_half_cost,0) + nvl(g_rule_in.adjustment_amount,0);
2312 x_first_half_cost_to_reduce
2313 := nvl(l_first_half_cost_to_reduce,0)+ nvl(g_rule_in.adjustment_amount,0)*nvl(g_rule_in.reduction_rate,0);
2314 end if;
2315
2316 -- When transaction is 1st half year, the amounts of 2nd half year are not changed.
2317 x_second_half_cost := nvl(l_second_half_cost,0);
2318 x_second_half_cost_to_reduce := nvl(l_second_half_cost_to_reduce,0);
2319
2320 else -- Second half year
2321 if g_rule_in.event_type ='RETIREMENT'
2322 or (g_rule_in.event_type ='AMORT_ADJ'
2323 and nvl(g_rule_in.member_transaction_type_code,'NULL') like '%RETIREMENT'
2324 and g_rule_in.asset_type ='GROUP')
2325 then
2326 x_second_half_cost := nvl(l_second_half_cost,0);
2327 x_second_half_cost_to_reduce:= nvl(l_second_half_cost_to_reduce,0);
2328 else
2329 x_second_half_cost
2330 := nvl(l_second_half_cost,0) + nvl(g_rule_in.adjustment_amount,0);
2331 x_second_half_cost_to_reduce
2332 := nvl(l_second_half_cost_to_reduce,0)
2333 + nvl(g_rule_in.adjustment_amount,0) *nvl(g_rule_in.reduction_rate,0);
2334 end if; -- event type
2335
2336 -- When transaction is 2nd half year, the amounts of 1st half year are not changed.
2337 x_first_half_cost := nvl(l_first_half_cost,0);
2338 x_first_half_cost_to_reduce := nvl(l_first_half_cost_to_reduce,0);
2339
2340 end if; -- End first half and second half year
2341
2342 else -- Reduction rate is null
2343
2344 x_change_in_cost := nvl(l_change_in_cost,0);
2345 x_change_in_cost_to_reduce := nvl(l_change_in_cost_to_reduce,0);
2346 x_net_proceeds := nvl(l_net_proceeds,0);
2347 x_net_proceeds_to_reduce := nvl(l_net_proceeds_to_reduce,0);
2348 x_first_half_cost := nvl(l_first_half_cost,0);
2349 x_first_half_cost_to_reduce := nvl(l_first_half_cost_to_reduce,0);
2350 x_second_half_cost := nvl(l_second_half_cost,0);
2351 x_second_half_cost_to_reduce := nvl(l_second_half_cost_to_reduce,0);
2352
2353 end if; -- Reduction rate is not null
2354
2355 else -- l_chk_count >0
2356
2357 x_change_in_cost := nvl(l_change_in_cost,0);
2358 x_change_in_cost_to_reduce := nvl(l_change_in_cost_to_reduce,0);
2359 x_net_proceeds := nvl(l_net_proceeds,0);
2360 x_net_proceeds_to_reduce := nvl(l_net_proceeds_to_reduce,0);
2361 x_first_half_cost := nvl(l_first_half_cost,0);
2362 x_first_half_cost_to_reduce := nvl(l_first_half_cost_to_reduce,0);
2363 x_second_half_cost := nvl(l_second_half_cost,0);
2364 x_second_half_cost_to_reduce := nvl(l_second_half_cost_to_reduce,0);
2365
2366 end if; -- End of l_chk_count
2367
2368 else
2369 x_total_change_in_cost := nvl(l_total_change_in_cost,0);
2370 x_total_net_proceeds := nvl(l_total_net_proceeds,0);
2371 x_change_in_cost := nvl(l_change_in_cost,0);
2372 x_change_in_cost_to_reduce := nvl(l_change_in_cost_to_reduce,0);
2373 x_net_proceeds := nvl(l_net_proceeds,0);
2374 x_net_proceeds_to_reduce := nvl(l_net_proceeds_to_reduce,0);
2375 x_first_half_cost := nvl(l_first_half_cost,0);
2376 x_first_half_cost_to_reduce := nvl(l_first_half_cost_to_reduce,0);
2377 x_second_half_cost := nvl(l_second_half_cost,0);
2378 x_second_half_cost_to_reduce := nvl(l_second_half_cost_to_reduce,0);
2379
2380 end if; -- Include this transaction to FY total
2381
2382 return true;
2383
2384 exception
2385 when no_mid_date_err then
2386 fa_srvr_msg.add_message (
2387 calling_fn => l_calling_fn,
2388 name => 'FA_NO_MID_YEAR_DATE',
2389 translate => FALSE
2390 ,p_log_level_rec => p_log_level_rec);
2391 return (FALSE);
2392
2393 when calc_reduction_amount_err then
2394 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2395 ,p_log_level_rec => p_log_level_rec);
2396 return (false);
2397
2398 when others then
2399 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2400 ,p_log_level_rec => p_log_level_rec);
2401 return (false);
2402
2403 end CALC_REDUCTION_AMOUNT;
2404
2405
2406 --------------------------------------------------------------
2407 -- Function: GET_REC_COST
2408 --
2409 -- This function is to get recoverable cost and salvage value
2410 -- at the period of parameter's period counter
2411 --
2412 -- p_asset_id : Asset Id
2413 -- p_book_type_code : Book Type Code
2414 -- p_fiscal_year : Fiscal Year
2415 -- p_period_num : Period Number
2416 -- p_asset_type : Asset Type
2417 -- p_recoverable_cost : Recoverable Cost at p_transaction_date_entered
2418 -- (Set only when p_transaction_date_entered is set)
2419 -- p_salvage_value : Salvage value at p_transaction_date_entered
2420 -- (Set only when p_transaction_date_entered is set)
2421 -- p_transaction_date_entered : Transaction Date Entered (INITIAL_ADDITION only)
2422 -- p_mrc_sob_type_code : MRC Set of Books type code
2423 -- x_recoverable_cost : Recoverable cost at the parameter's period
2424 -- x_salvage_value : Salvage value at the parameter's period
2425 -------------------------------------------------------------
2426 FUNCTION GET_REC_COST
2427 (
2428 p_asset_id IN NUMBER,
2429 p_book_type_code IN VARCHAR2,
2430 p_fiscal_year IN NUMBER,
2431 p_period_num IN NUMBER,
2432 p_asset_type IN VARCHAR2,
2433 p_recoverable_cost IN NUMBER,
2434 p_salvage_value IN NUMBER,
2435 p_transaction_date_entered IN DATE,
2436 p_mrc_sob_type_code IN VARCHAR2,
2437 x_recoverable_cost OUT NOCOPY NUMBER,
2438 x_salvage_value OUT NOCOPY NUMBER,
2439 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2440 return boolean is
2441
2442 l_period_close_date date;
2443 l_fy_start_date date;
2444 l_fy_end_date date;
2445
2446 -- Get Recoverable cost for Member and Standalone asset
2447 cursor C_GET_REC_COST is
2448 select sum(BK2.RECOVERABLE_COST -nvl(BK1.RECOVERABLE_COST,0)) recoverable_cost,
2449 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0)) salvage_value
2450 from FA_BOOKS BK1,
2451 FA_BOOKS BK2,
2452 FA_TRANSACTION_HEADERS TH
2453 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
2454 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
2455 and TH.ASSET_ID = p_asset_id
2456 and TH.BOOK_TYPE_CODE = p_book_type_code
2457 and TH.TRANSACTION_DATE_ENTERED <= l_period_close_date;
2458
2459 cursor C_GET_REC_COST_MRC is
2460 select sum(BK2.RECOVERABLE_COST -nvl(BK1.RECOVERABLE_COST,0)) recoverable_cost,
2461 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0)) salvage_value
2462 from FA_BOOKS_MRC_V BK1,
2463 FA_BOOKS_MRC_V BK2,
2464 FA_TRANSACTION_HEADERS TH
2465 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
2466 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
2467 and TH.ASSET_ID = p_asset_id
2468 and TH.BOOK_TYPE_CODE = p_book_type_code
2469 and TH.TRANSACTION_DATE_ENTERED <= l_period_close_date;
2470
2471 -- Get Recoverable cost for Group Asset
2472 -- For Reclass, get sum of delta recoverable cost and salvage value from member asset
2473 -- bug4943259: removed FA_FISCAL_YEAR and FA_CALENDAR_TYPES
2474 cursor GP_GET_REC_COST is
2475 select sum(BK2.COST -nvl(BK1.COST,0))
2476 - decode(BK3.SALVAGE_TYPE,
2477 'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
2478 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) recoverable_cost,
2479 decode(BK3.SALVAGE_TYPE,
2480 'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
2481 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) salvage_value
2482 from FA_BOOKS BK1,
2483 FA_BOOKS BK2,
2484 FA_BOOKS BK3,
2485 FA_TRANSACTION_HEADERS TH,
2486 FA_CALENDAR_PERIODS CP
2487 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
2488 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
2489 and BK2.BOOK_TYPE_CODE = p_book_type_code
2490 and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
2491 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbc_record.deprn_calendar
2492 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
2493 and CP.END_DATE <= l_fy_end_date
2494 and CP.END_DATE >= l_fy_start_date
2495 and CP.PERIOD_NUM = p_period_num
2496 and exists (select TH.ASSET_ID
2497 from FA_BOOKS BK4
2498 where TH.ASSET_ID = BK4.ASSET_ID and
2499 BK4.BOOK_TYPE_CODE = p_book_type_code and
2500 BK4.GROUP_ASSET_ID = p_asset_id and
2501 BK4. DATE_INEFFECTIVE is null)
2502 and BK3.TRANSACTION_HEADER_ID_IN=
2503 (select max(BK.TRANSACTION_HEADER_ID_IN)
2504 from FA_BOOKS BK,
2505 FA_TRANSACTION_HEADERS TH,
2506 FA_CALENDAR_PERIODS CP
2507 where BK.ASSET_ID= p_asset_id
2508 and BK.BOOK_TYPE_CODE = p_book_type_code
2509 and BK.TRANSACTION_HEADER_ID_IN =TH.TRANSACTION_HEADER_ID
2510 and BK.ASSET_ID= TH.ASSET_ID
2511 and BK.BOOK_TYPE_CODE= TH.BOOK_TYPE_CODE
2512 and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
2513 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbc_record.deprn_calendar
2514 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
2515 and CP.END_DATE <= l_fy_end_date
2516 and CP.END_DATE >= l_fy_start_date
2517 and CP.PERIOD_NUM = p_period_num
2518 )
2519 group by BK3.SALVAGE_TYPE,BK3.PERCENT_SALVAGE_VALUE;
2520
2521 cursor GP_GET_REC_COST_MRC is
2522 select sum(BK2.COST -nvl(BK1.COST,0))
2523 - decode(BK3.SALVAGE_TYPE,
2524 'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
2525 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) recoverable_cost,
2526 decode(BK3.SALVAGE_TYPE,
2527 'PCT', sum(BK2.COST -nvl(BK1.COST,0))* nvl(BK3.PERCENT_SALVAGE_VALUE,0),
2528 sum(BK2.SALVAGE_VALUE -nvl(BK1.SALVAGE_VALUE,0))) salvage_value
2529 from FA_BOOKS_MRC_V BK1,
2530 FA_BOOKS_MRC_V BK2,
2531 FA_BOOKS_MRC_V BK3,
2532 FA_TRANSACTION_HEADERS TH,
2533 FA_CALENDAR_PERIODS CP
2534 where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
2535 and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
2536 and BK2.BOOK_TYPE_CODE = p_book_type_code
2537 and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
2538 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbcs_record.deprn_calendar
2539 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
2540 and CP.END_DATE <= l_fy_end_date
2541 and CP.END_DATE >= l_fy_start_date
2542 and CP.PERIOD_NUM = p_period_num
2543 and exists (select TH.ASSET_ID
2544 from FA_BOOKS_MRC_V BK4
2545 where TH.ASSET_ID = BK4.ASSET_ID and
2546 BK4.BOOK_TYPE_CODE = p_book_type_code and
2547 BK4.GROUP_ASSET_ID = p_asset_id and
2548 BK4. DATE_INEFFECTIVE is null)
2549 and BK3.TRANSACTION_HEADER_ID_IN=
2550 (select max(BK.TRANSACTION_HEADER_ID_IN)
2551 from FA_BOOKS_MRC_V BK,
2552 FA_TRANSACTION_HEADERS TH,
2553 FA_CALENDAR_PERIODS CP,
2554 FA_BOOK_CONTROLS_MRC_V BC
2555 where BK.ASSET_ID= p_asset_id
2556 and BK.BOOK_TYPE_CODE = p_book_type_code
2557 and BK.TRANSACTION_HEADER_ID_IN =TH.TRANSACTION_HEADER_ID
2558 and BK.ASSET_ID= TH.ASSET_ID
2559 and BK.BOOK_TYPE_CODE= TH.BOOK_TYPE_CODE
2560 and TH.TRANSACTION_DATE_ENTERED <= CP.END_DATE
2561 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcbcs_record.deprn_calendar
2562 and CP.CALENDAR_TYPE = fa_cache_pkg.fazcct_record.calendar_type
2563 and CP.END_DATE <= l_fy_end_date
2564 and CP.END_DATE >= l_fy_start_date
2565 and CP.PERIOD_NUM = p_period_num
2566 )
2567 group by BK3.SALVAGE_TYPE,BK3.PERCENT_SALVAGE_VALUE;
2568
2569 -- Find fiscal Year record
2570 cursor c_find_fy is
2571 select start_date
2572 , end_date
2573 from fa_fiscal_year
2574 where fiscal_year_name = fa_cache_pkg.fazcbc_record.fiscal_year_name
2575 and fiscal_year = p_fiscal_year;
2576
2577
2578 -- Get period close date
2579 cursor C_PERIOD_CLOSE_DATE is
2580 select CP.END_DATE
2581 from FA_CALENDAR_PERIODS CP,
2582 FA_CALENDAR_TYPES CT,
2583 FA_FISCAL_YEAR FY,
2584 FA_BOOK_CONTROLS BC
2585 where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
2586 and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
2587 and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
2588 and CP.END_DATE <= FY.END_DATE
2589 and CP.END_DATE >= FY.START_DATE
2590 and BC.BOOK_TYPE_CODE= p_book_type_code
2591 and FY.FISCAL_YEAR = p_fiscal_year
2592 and CP.PERIOD_NUM = p_period_num;
2593
2594 cursor C_PERIOD_CLOSE_DATE_MRC is
2595 select CP.END_DATE
2596 from FA_CALENDAR_PERIODS CP,
2597 FA_CALENDAR_TYPES CT,
2598 FA_FISCAL_YEAR FY,
2599 FA_BOOK_CONTROLS_MRC_V BC
2600 where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
2601 and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
2602 and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
2603 and CP.END_DATE <= FY.END_DATE
2604 and CP.END_DATE >= FY.START_DATE
2605 and BC.BOOK_TYPE_CODE= p_book_type_code
2606 and FY.FISCAL_YEAR = p_fiscal_year
2607 and CP.PERIOD_NUM = p_period_num;
2608
2609 cursor C_LAST_AMOUNT is
2610 select BK.RECOVERABLE_COST,
2611 BK.SALVAGE_VALUE
2612 from FA_BOOKS BK
2613 where BK.ASSET_ID = p_asset_id
2614 and BK.BOOK_TYPE_CODE = p_book_type_code
2615 and BK.TRANSACTION_HEADER_ID_OUT is null;
2616
2617 cursor C_LAST_AMOUNT_MRC is
2618 select BK.RECOVERABLE_COST,
2619 BK.SALVAGE_VALUE
2620 from FA_BOOKS_MRC_V BK
2621 where BK.ASSET_ID = p_asset_id
2622 and BK.BOOK_TYPE_CODE = p_book_type_code
2623 and BK.TRANSACTION_HEADER_ID_OUT is null;
2624
2625 -- l_period_close_date date;
2626 l_last_recoverable_cost number;
2627 l_last_salvage_value number;
2628
2629 l_calling_fn varchar2(40) := 'fa_calc_deprn_basis1_pkg.GET_REC_COST';
2630
2631 grc_err exception;
2632
2633 begin
2634
2635 -- Skipping entire process for straight line and flat-cost PE and User rec cost deprn basis
2636 -- rule because it is unnecessary.
2637 if (not(fa_cache_pkg.fazccmt_record.rate_source_rule = fa_std_types.FAD_RSR_CALC or
2638 (fa_cache_pkg.fazccmt_record.rate_source_rule = fa_std_types.FAD_RSR_FLAT and
2639 fa_cache_pkg.fazccmt_record.deprn_basis_rule = fa_std_types.FAD_DBR_COST and
2640 fa_cache_pkg.fazcdbr_record.rule_name in ('PERIOD END BALANCE', 'USE RECOVERABLE COST'))
2641 )) then
2642
2643 -------------------------------------------------
2644 -- Treate this transaction's
2645 -- Adjustment recoverable cost and salvage value
2646 -------------------------------------------------
2647
2648 -- the following global variables are used to improve the performance.
2649
2650 if p_mrc_sob_type_code <>'R' then
2651 if p_fiscal_year = nvl(g_fiscal_year1, -99) and
2652 p_period_num = nvl(g_period_num1, -99) and
2653 p_book_type_code = g_book_type_code then
2654
2655 l_period_close_date:= g_end_date1;
2656
2657 elsif p_fiscal_year = nvl(g_fiscal_year2,-99) and
2658 p_period_num = nvl(g_period_num2, -99) and
2659 p_book_type_code = g_book_type_code then
2660
2661 l_period_close_date:= g_end_date2;
2662
2663 else
2664 OPEN C_PERIOD_CLOSE_DATE;
2665 FETCH C_PERIOD_CLOSE_DATE into l_period_close_date;
2666 CLOSE C_PERIOD_CLOSE_DATE;
2667
2668 if g_period_num1 is null then
2669 g_fiscal_year1:= p_fiscal_year;
2670 g_period_num1:= nvl(p_period_num, -99);
2671 g_end_date1:= l_period_close_date;
2672 else
2673 g_fiscal_year2:= p_fiscal_year;
2674 g_period_num2:= nvl(p_period_num, -99);
2675 g_end_date2:= l_period_close_date;
2676 end if;
2677 end if;
2678 else
2679
2680 if p_fiscal_year = nvl(g_fiscal_year1, -99) and
2681 p_period_num = nvl(g_period_num1, -99) and
2682 p_book_type_code = g_book_type_code then
2683
2684 l_period_close_date:= g_end_date1;
2685
2686 elsif p_fiscal_year = nvl(g_fiscal_year2, -99) and
2687 p_period_num = nvl(g_period_num2, -99) and
2688 p_book_type_code = g_book_type_code then
2689
2690 l_period_close_date:= g_end_date2;
2691
2692 else
2693 OPEN C_PERIOD_CLOSE_DATE_MRC;
2694 FETCH C_PERIOD_CLOSE_DATE_MRC into l_period_close_date;
2695 CLOSE C_PERIOD_CLOSE_DATE_MRC;
2696
2697 if g_period_num1 is null then
2698 g_fiscal_year1:= p_fiscal_year;
2699 g_period_num1:= p_period_num;
2700 g_end_date1:= l_period_close_date;
2701 else
2702 g_fiscal_year2:= p_fiscal_year;
2703 g_period_num2:= p_period_num;
2704 g_end_date2:= l_period_close_date;
2705 end if;
2706 end if;
2707 end if;
2708
2709 -- bug4943259: Added function calls fazcct fazcfy
2710 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar,
2711 p_log_level_rec) then
2712 raise grc_err;
2713 end if;
2714
2715 if not fa_cache_pkg.fazcfy(fa_cache_pkg.fazcbc_record.fiscal_year_name,
2716 p_fiscal_year,
2717 p_log_level_rec) then
2718 -- find out whether fazcfy returned false because of missing record or not.
2719 OPEN c_find_fy;
2720 FETCH c_find_fy INTO l_fy_start_date, l_fy_end_date;
2721
2722 if c_find_fy%NOTFOUND then
2723 CLOSE c_find_fy;
2724 l_fy_start_date := null;
2725 l_fy_end_date := null;
2726 else
2727 CLOSE c_find_fy;
2728 raise grc_err;
2729 end if;
2730 else
2731 l_fy_start_date := fa_cache_pkg.fazcfy_record.start_date;
2732 l_fy_end_date := fa_cache_pkg.fazcfy_record.end_date;
2733 end if;
2734
2735
2736 if p_mrc_sob_type_code <>'R' then
2737
2738 if p_asset_type='GROUP' then -- Group Asset
2739 open GP_GET_REC_COST;
2740 fetch GP_GET_REC_COST into x_recoverable_cost, x_salvage_value;
2741 if GP_GET_REC_COST%NOTFOUND then
2742 x_recoverable_cost := 0;
2743 x_salvage_value := 0;
2744 end if;
2745 close GP_GET_REC_COST;
2746
2747 else -- Member and stand alone asset
2748
2749 open C_GET_REC_COST;
2750 fetch C_GET_REC_COST into x_recoverable_cost, x_salvage_value;
2751 if C_GET_REC_COST%NOTFOUND then
2752 x_recoverable_cost := 0;
2753 x_salvage_value := 0;
2754 end if;
2755 close C_GET_REC_COST;
2756 end if;
2757
2758 else -- MRC
2759
2760 if p_asset_type='GROUP' then -- Group Asset
2761 open GP_GET_REC_COST_MRC;
2762 fetch GP_GET_REC_COST_MRC into x_recoverable_cost, x_salvage_value;
2763 if GP_GET_REC_COST_MRC%NOTFOUND then
2764 x_recoverable_cost := 0;
2765 x_salvage_value := 0;
2766 end if;
2767 close GP_GET_REC_COST_MRC;
2768
2769 else -- Member and stand alone asset
2770
2771 open C_GET_REC_COST_MRC;
2772 fetch C_GET_REC_COST_MRC into x_recoverable_cost, x_salvage_value;
2773 if C_GET_REC_COST_MRC%NOTFOUND then
2774 x_recoverable_cost := 0;
2775 x_salvage_value := 0;
2776 end if;
2777 close C_GET_REC_COST_MRC;
2778 end if;
2779
2780 end if; -- End of MRC
2781
2782 -------------------------------------------------
2783 -- Treate this transaction's
2784 -- Adjustment recoverable cost and salvage value
2785 -------------------------------------------------
2786
2787 -- if p_mrc_sob_type_code <>'R' then
2788 -- OPEN C_PERIOD_CLOSE_DATE;
2789 -- FETCH C_PERIOD_CLOSE_DATE into l_period_close_date;
2790 -- CLOSE C_PERIOD_CLOSE_DATE;
2791 -- else
2792 -- OPEN C_PERIOD_CLOSE_DATE_MRC;
2793 -- FETCH C_PERIOD_CLOSE_DATE_MRC into l_period_close_date;
2794 -- CLOSE C_PERIOD_CLOSE_DATE_MRC;
2795 -- end if;
2796
2797 if p_transaction_date_entered is not null
2798 and p_transaction_date_entered <= l_period_close_date
2799 and p_recoverable_cost is not null
2800 and p_salvage_value is not null
2801 then
2802 if p_mrc_sob_type_code <>'R' then
2803
2804 OPEN C_LAST_AMOUNT;
2805 FETCH C_LAST_AMOUNT into l_last_recoverable_cost, l_last_salvage_value;
2806 CLOSE C_LAST_AMOUNT;
2807 else
2808 OPEN C_LAST_AMOUNT_MRC;
2809 FETCH C_LAST_AMOUNT_MRC into l_last_recoverable_cost, l_last_salvage_value;
2810 CLOSE C_LAST_AMOUNT_MRC;
2811
2812 end if;
2813
2814 x_recoverable_cost := nvl(x_recoverable_cost,0)
2815 + nvl(p_recoverable_cost,0) - nvl(l_last_recoverable_cost,0);
2816 x_salvage_value := nvl(x_salvage_value,0)
2817 + nvl(p_salvage_value,0) - nvl(l_last_salvage_value,0);
2818 end if;
2819
2820 end if;
2821
2822 ------------------------------------------------------------
2823 -- If x_recoverable_cost and x_salvage_value is set null,
2824 -- Set 0 to them
2825 ------------------------------------------------------------
2826 x_recoverable_cost := nvl(x_recoverable_cost,0);
2827 x_salvage_value := nvl(x_salvage_value,0);
2828
2829 return true;
2830
2831 exception
2832 when grc_err then
2833 -- bug4943259: Added this exception in case new added cache functions
2834 -- returns error
2835 if p_log_level_rec.statement_level then
2836 fa_debug_pkg.add(fname=> l_calling_fn,
2837 element=>'Error calling ',
2838 value=> 'Cashe function',
2839 p_log_level_rec => p_log_level_rec);
2840 end if;
2841
2842 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2843 ,p_log_level_rec => p_log_level_rec);
2844
2845 return (false);
2846 when others then
2847 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
2848 ,p_log_level_rec => p_log_level_rec);
2849
2850 return (false);
2851
2852 END GET_REC_COST;
2853
2854 --------------------------------------------------------------
2855 -- Function: GET_EOFY_EOP
2856 --
2857 -- This function is to get recoverable cost and salvage value
2858 -- at the end of last fiscal year and last period
2859 -- p_asset_id : Asset Id
2860 -- p_book_type_code : Book Type Code
2861 -- p_fiscal_year : Fiscal Year
2862 -- p_period_num : Period Number
2863 -- p_asset_type : Asset Type
2864 -- p_recoverable_cost : Recoverable Cost at p_transaction_date_entered
2865 -- (Set only when p_transaction_date_entered is set)
2866 -- p_salvage_value : Salvage value at p_transaction_date_entered
2867 -- (Set only when p_transaction_date_entered is set)
2868 -- p_transaction_date_entered : Transaction Date Entered (INITIAL_ADDITION only)
2869 -- p_period_counter : Period counter
2870 -- p_mrc_sob_type_code : MRC Set of Books type code
2871 -- x_recoverable_cost : Recoverable cost at the parameter's period
2872 -- x_salvage_value : Salvage value at the parameter's period
2873 -------------------------------------------------------------
2874
2875 FUNCTION GET_EOFY_EOP
2876 (
2877 p_asset_id IN NUMBER,
2878 p_book_type_code IN VARCHAR2,
2879 p_fiscal_year IN NUMBER,
2880 p_period_num IN NUMBER,
2881 p_asset_type IN VARCHAR2,
2882 p_recoverable_cost IN NUMBER,
2883 p_salvage_value IN NUMBER,
2884 p_transaction_date_entered IN DATE,
2885 p_period_counter IN NUMBER,
2886 p_mrc_sob_type_code IN VARCHAR2,
2887 x_eofy_recoverable_cost OUT NOCOPY NUMBER,
2888 x_eofy_salvage_value OUT NOCOPY NUMBER,
2889 x_eop_recoverable_cost OUT NOCOPY NUMBER,
2890 x_eop_salvage_value OUT NOCOPY NUMBER,
2891 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
2892 return boolean is
2893
2894 cursor C_GET_NPFY
2895 is
2896 select CT.number_per_fiscal_year
2897 from FA_CALENDAR_TYPES CT,
2898 FA_BOOK_CONTROLS BC
2899 where BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE
2900 and BC.BOOK_TYPE_CODE= p_book_type_code;
2901
2902 cursor C_GET_NPFY_MRC
2903 is
2904 select CT.number_per_fiscal_year
2905 from FA_CALENDAR_TYPES CT,
2906 FA_BOOK_CONTROLS_MRC_V BC
2907 where BC.DEPRN_CALENDAR = CT.CALENDAR_TYPE
2908 and BC.BOOK_TYPE_CODE= p_book_type_code;
2909
2910 CURSOR c_get_bs_amounts(c_period_counter number) IS
2911 select bs.recoverable_cost
2912 , bs.salvage_value
2913 from fa_books_summary bs
2914 where bs.asset_id = p_asset_id
2915 and bs.book_type_code = p_book_type_code
2916 and bs.period_counter = c_period_counter;
2917
2918 CURSOR c_get_mc_bs_amounts(c_period_counter number) IS
2919 select bs.recoverable_cost
2920 , bs.salvage_value
2921 from fa_books_summary_mrc_v bs
2922 where bs.asset_id = p_asset_id
2923 and bs.book_type_code = p_book_type_code
2924 and bs.period_counter = c_period_counter;
2925
2926
2927 h_num_per_fy number(15); -- Number per fiscal year
2928
2929 h_eofy_period_num number(15) :=null;
2930 h_eop_period_num number(15) :=null;
2931 h_eop_fiscal_year number(15) :=null;
2932
2933 l_period_counter number(15);
2934 l_temp_period_counter number(15);
2935 l_rec_cost number;
2936 l_salvage_value number;
2937
2938 l_calling_fn varchar2(40) := 'fa_calc_deprn_basis1_pkg.GET_EOFY_EOP';
2939
2940 l_get_eofy_eop_err exception;
2941
2942 begin
2943 if p_log_level_rec.statement_level then
2944 fa_debug_pkg.add(l_calling_fn, 'p_period_counter:p_period_num', to_char(p_period_counter)||':'||
2945 to_char(p_period_num));
2946 end if;
2947
2948 if (p_mrc_sob_type_code = 'R') then
2949 if NOT fa_cache_pkg.fazcbcs(X_book => p_book_type_code,
2950 p_log_level_rec => p_log_level_rec) then
2951 raise l_get_eofy_eop_err;
2952 end if;
2953 else
2954 -- call the cache for the primary transaction book
2955 if NOT fa_cache_pkg.fazcbc(X_book => p_book_type_code,
2956 p_log_level_rec => p_log_level_rec) then
2957 raise l_get_eofy_eop_err;
2958 end if;
2959 end if;
2960
2961
2962 if p_asset_type = 'GROUP' then
2963 if p_period_counter is null then
2964
2965 if not fa_cache_pkg.fazcct(fa_cache_pkg.fazcbc_record.deprn_calendar) then
2966 raise l_get_eofy_eop_err;
2967 end if;
2968
2969 l_period_counter := p_fiscal_year *
2970 fa_cache_pkg.fazcct_record.NUMBER_PER_FISCAL_YEAR +
2971 p_period_num;
2972
2973 else
2974
2975 l_period_counter := p_period_counter;
2976 end if;
2977
2978 --
2979 -- eop/eofy amounts are available in FA_BOOKS_SUMMARY table.
2980 --
2981
2982 if (p_mrc_sob_type_code = 'R') then
2983 FOR i in 1..2 LOOP
2984 --
2985 -- if i = 1, looking for eofy amounts
2986 -- is i = 2, looking for eop amounts
2987 if i = 1 then
2988 l_temp_period_counter := l_period_counter - p_period_num;
2989 else
2990 l_temp_period_counter := l_period_counter - 1;
2991 end if;
2992
2993 OPEN c_get_mc_bs_amounts(l_temp_period_counter);
2994 FETCH c_get_mc_bs_amounts INTO l_rec_cost, l_salvage_value;
2995
2996 if (c_get_mc_bs_amounts%NOTFOUND) then
2997 if i = 1 then
2998 x_eofy_recoverable_cost := 0;
2999 x_eofy_salvage_value := 0;
3000 else
3001 x_eop_recoverable_cost := 0;
3002 x_eop_salvage_value := 0;
3003 end if;
3004 else
3005 if i = 1 then
3006 x_eofy_recoverable_cost := l_rec_cost;
3007 x_eofy_salvage_value := l_salvage_value;
3008 else
3009 x_eop_recoverable_cost := l_rec_cost;
3010 x_eop_salvage_value := l_salvage_value;
3011 end if;
3012 end if;
3013
3014 CLOSE c_get_mc_bs_amounts;
3015
3016 END LOOP;
3017 else
3018 FOR i in 1..2 LOOP
3019 --
3020 -- if i = 1, looking for eofy amounts
3021 -- is i = 2, looking for eop amounts
3022 if i = 1 then
3023 l_temp_period_counter := l_period_counter - p_period_num;
3024 else
3025 l_temp_period_counter := l_period_counter - 1;
3026 end if;
3027
3028 OPEN c_get_bs_amounts(l_temp_period_counter);
3029 FETCH c_get_bs_amounts INTO l_rec_cost, l_salvage_value;
3030
3031 if (c_get_bs_amounts%NOTFOUND) then
3032 if i = 1 then
3033 x_eofy_recoverable_cost := 0;
3034 x_eofy_salvage_value := 0;
3035 else
3036 x_eop_recoverable_cost := 0;
3037 x_eop_salvage_value := 0;
3038 end if;
3039 else
3040 if i = 1 then
3041 x_eofy_recoverable_cost := l_rec_cost;
3042 x_eofy_salvage_value := l_salvage_value;
3043 else
3044 x_eop_recoverable_cost := l_rec_cost;
3045 x_eop_salvage_value := l_salvage_value;
3046 end if;
3047 end if;
3048
3049 CLOSE c_get_bs_amounts;
3050
3051 END LOOP;
3052 end if;
3053
3054 if p_log_level_rec.statement_level then
3055 fa_debug_pkg.add(fname=>'GET_EOFY_EOP',
3056 element=>'Found amounts from BS',
3057 value=> to_char(x_eofy_recoverable_cost)||':'||
3058 to_char(x_eofy_salvage_value)||':'||
3059 to_char(x_eop_recoverable_cost)||':'||
3060 to_char(x_eop_salvage_value));
3061 end if;
3062
3063 else
3064 -------------------------------------------
3065 -- Query number per fiscal year
3066 -------------------------------------------
3067
3068 if p_mrc_sob_type_code <> 'R' then
3069 if g_book_type_code is null or p_book_type_code <> g_book_type_code then
3070 OPEN C_GET_NPFY;
3071 FETCH C_GET_NPFY into h_num_per_fy;
3072 CLOSE C_GET_NPFY;
3073 g_book_type_code:= p_book_type_code;
3074 g_num_per_fy:= h_num_per_fy;
3075 else
3076 h_num_per_fy:= g_num_per_fy;
3077 end if;
3078 else
3079 -- to use global variable here, we need org_id too.
3080 OPEN C_GET_NPFY_MRC;
3081 FETCH C_GET_NPFY_MRC into h_num_per_fy;
3082 CLOSE C_GET_NPFY_MRC;
3083 end if;
3084
3085 -------------------------------------------
3086 -- Get recoverable cost and salvage value
3087 -- at the end of last fiscal year
3088 -------------------------------------------
3089
3090 h_eofy_period_num := h_num_per_fy;
3091
3092 if (not FA_CALC_DEPRN_BASIS1_PKG.GET_REC_COST
3093 (
3094 p_asset_id => p_asset_id,
3095 p_book_type_code => p_book_type_code,
3096 p_fiscal_year => p_fiscal_year -1,
3097 p_period_num => h_eofy_period_num,
3098 p_asset_type => p_asset_type,
3099 p_recoverable_cost => p_recoverable_cost,
3100 p_salvage_value => p_salvage_value,
3101 p_transaction_date_entered => p_transaction_date_entered,
3102 p_mrc_sob_type_code => p_mrc_sob_type_code,
3103 x_recoverable_cost => x_eofy_recoverable_cost,
3104 x_salvage_value => x_eofy_salvage_value
3105
3106 ,p_log_level_rec => p_log_level_rec))
3107 then
3108 raise l_get_eofy_eop_err;
3109 end if;
3110
3111
3112
3113 -------------------------------------------
3114 -- Get recoverable cost and salvage value
3115 -- at the end of last period
3116 -------------------------------------------
3117 if p_period_num -1 = 0 then
3118 h_eop_period_num := h_num_per_fy;
3119 h_eop_fiscal_year := p_fiscal_year -1;
3120 else
3121 h_eop_period_num := p_period_num -1;
3122 h_eop_fiscal_year := p_fiscal_year;
3123 end if;
3124
3125 if (not FA_CALC_DEPRN_BASIS1_PKG.GET_REC_COST
3126 (
3127 p_asset_id => p_asset_id,
3128 p_book_type_code => p_book_type_code,
3129 p_fiscal_year => h_eop_fiscal_year,
3130 p_period_num => h_eop_period_num,
3131 p_asset_type => p_asset_type,
3132 p_recoverable_cost => p_recoverable_cost,
3133 p_salvage_value => p_salvage_value,
3134 p_transaction_date_entered => p_transaction_date_entered,
3135 p_mrc_sob_type_code => p_mrc_sob_type_code,
3136 x_recoverable_cost => x_eop_recoverable_cost,
3137 x_salvage_value => x_eop_salvage_value
3138
3139 ,p_log_level_rec => p_log_level_rec))
3140 then
3141 raise l_get_eofy_eop_err;
3142 end if;
3143 end if; -- (p_period_counter is not null and
3144
3145 return true;
3146
3147 exception
3148 when l_get_eofy_eop_err then
3149 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3150 ,p_log_level_rec => p_log_level_rec);
3151 return (false);
3152
3153 when others then
3154 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3155 ,p_log_level_rec => p_log_level_rec);
3156 return (false);
3157
3158 END GET_EOFY_EOP;
3159
3160 --------------------------------------------------------
3161 -- Function: CALL_DEPRN_BASIS
3162 --
3163 -- called from depreciation Engine(pl/sql)
3164 -- This is same as CALL_DEPRN_BASIS above except additional
3165 -- parameter x_annual_deprn_rounding_flag
3166 -- This new output is necessary for flat rate extension
3167 --------------------------------------------------------
3168
3169 FUNCTION CALL_DEPRN_BASIS(
3170 p_event_type IN varchar2,
3171 p_asset_fin_rec_new IN fa_api_types.asset_fin_rec_type,
3172 p_asset_fin_rec_old IN fa_api_types.asset_fin_rec_type,
3173 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
3174 p_asset_type_rec IN fa_api_types.asset_type_rec_type,
3175 p_asset_deprn_rec IN fa_api_types.asset_deprn_rec_type,
3176 p_trans_rec IN fa_api_types.trans_rec_type,
3177 p_trans_rec_adj IN fa_api_types.trans_rec_type,
3178 p_period_rec IN fa_api_types.period_rec_type,
3179 p_asset_retire_rec IN fa_api_types.asset_retire_rec_type,
3180 p_unplanned_deprn_rec IN fa_api_types.unplanned_deprn_rec_type,
3181 p_dpr IN fa_std_types.dpr_struct,
3182 p_fiscal_year IN number,
3183 p_period_num IN number,
3184 p_period_counter IN number,
3185 p_recoverable_cost IN number,
3186 p_salvage_value IN number,
3187 p_adjusted_cost IN number,
3188 p_current_total_rsv IN number,
3189 p_current_rsv IN number,
3190 p_current_total_ytd IN number,
3191 p_current_ytd IN number,
3192 p_hyp_basis IN number,
3193 p_hyp_total_rsv IN number,
3194 p_hyp_rsv IN number,
3195 p_hyp_total_ytd IN number,
3196 p_hyp_ytd IN number,
3197 p_eofy_recoverable_cost IN number,
3198 p_eop_recoverable_cost IN number,
3199 p_eofy_salvage_value IN number,
3200 p_eop_salvage_value IN number,
3201 p_eofy_reserve IN number,
3202 p_adj_reserve IN number,
3203 p_reserve_retired IN number,
3204 p_used_by_adjustment IN varchar2,
3205 p_eofy_flag IN varchar2,
3206 p_apply_reduction_flag IN varchar2,
3207 p_mrc_sob_type_code IN varchar2,
3208 px_new_adjusted_cost IN OUT NOCOPY number,
3209 px_new_raf IN OUT NOCOPY number,
3210 px_new_formula_factor IN OUT NOCOPY number,
3211 x_annual_deprn_rounding_flag IN OUT NOCOPY varchar2,
3212 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return BOOLEAN IS
3213
3214 h_rule_in fa_std_types.fa_deprn_rule_in_struct;
3215 h_rule_out fa_std_types.fa_deprn_rule_out_struct;
3216
3217 tmp_method_code varchar2(12);
3218 tmp_life_in_months number(4);
3219
3220 l_calling_fn varchar2(50) := 'fa_calc_deprn_basis1_pkg.CALL_DEPRN_BASIS';
3221
3222 call_deprn_basis_err exception;
3223
3224 BEGIN
3225
3226 -------------
3227 -- Debug
3228 -------------
3229 if p_log_level_rec.statement_level then
3230 fa_debug_pkg.add(fname=>'CALL_DEPRN_BASIS',
3231 element=>'p_event_type',
3232 value=> p_event_type
3233 ,p_log_level_rec => p_log_level_rec);
3234 end if;
3235
3236 -------------------------------------------
3237 -- Get Method information
3238 -------------------------------------------
3239
3240 if p_event_type ='AFTER_DEPRN' then
3241 tmp_method_code := p_dpr.method_code;
3242 tmp_life_in_months := p_dpr.life;
3243 else
3244 tmp_method_code := p_asset_fin_rec_new.Deprn_Method_Code;
3245 tmp_life_in_months := p_asset_fin_rec_new.Life_In_Months;
3246 end if;
3247
3248 if (fa_cache_pkg.fazccmt_record.rate_source_rule is null) or
3249 ((tmp_method_code <> fa_cache_pkg.fazccmt_record.method_code) or
3250 (nvl(tmp_life_in_months, -99) <> nvl(fa_cache_pkg.fazccmt_record.life_in_months, -99))) then
3251 if not fa_cache_pkg.fazccmt
3252 (X_method => tmp_method_code,
3253 X_life => tmp_life_in_months
3254 ,p_log_level_rec => p_log_level_rec) then
3255
3256 raise call_deprn_basis_err;
3257 end if;
3258 end if;
3259
3260
3261 ----------------------------------------------------
3262 -- Set variables by default
3263 ----------------------------------------------------
3264
3265 h_rule_in.event_type := p_event_type;
3266 h_rule_in.asset_id := p_asset_hdr_rec.asset_id;
3267 h_rule_in.group_asset_id := p_asset_fin_rec_new.group_asset_id;
3268 h_rule_in.book_type_code := p_asset_hdr_rec.book_type_code;
3269 h_rule_in.asset_type := nvl(p_dpr.asset_type, p_asset_type_rec.asset_type);
3270 h_rule_in.depreciate_flag := p_asset_fin_rec_new.depreciate_flag;
3271 h_rule_in.method_code := p_asset_fin_rec_new.deprn_method_code;
3272 h_rule_in.life_in_months := p_asset_fin_rec_new.life_in_months;
3273 h_rule_in.method_id := fa_cache_pkg.fazccmt_record.method_id;
3274 h_rule_in.method_type := fa_cache_pkg.fazccmt_record.rate_source_rule;
3275 h_rule_in.calc_basis := fa_cache_pkg.fazccmt_record.deprn_basis_rule;
3276 h_rule_in.adjustment_amount := nvl(p_asset_fin_rec_new.cost,0)
3277 - nvl(p_asset_fin_rec_old.cost,0);
3278 h_rule_in.transaction_flag := null;
3279 h_rule_in.cost := nvl(p_asset_fin_rec_new.cost,0);
3280 h_rule_in.salvage_value := nvl(p_asset_fin_rec_new.salvage_value,0);
3281
3282 h_rule_in.recoverable_cost := nvl(p_asset_fin_rec_new.recoverable_cost,0);
3283 h_rule_in.adjusted_cost := nvl(p_asset_fin_rec_old.adjusted_cost,0);
3284 h_rule_in.current_total_rsv := nvl(p_asset_deprn_rec.deprn_reserve,0);
3285 h_rule_in.current_rsv := nvl(p_asset_deprn_rec.deprn_reserve,0);
3286 h_rule_in.current_total_ytd := nvl(p_asset_deprn_rec.ytd_deprn,0);
3287 h_rule_in.current_ytd := nvl(p_asset_deprn_rec.ytd_deprn,0);
3288 h_rule_in.hyp_basis := 0;
3289 h_rule_in.hyp_total_rsv := 0;
3290 h_rule_in.hyp_rsv := 0;
3291 h_rule_in.hyp_total_ytd := 0;
3292 h_rule_in.hyp_ytd := 0;
3293 h_rule_in.old_cost := nvl(p_asset_fin_rec_old.cost,0);
3294 h_rule_in.old_adjusted_cost := nvl(p_asset_fin_rec_old.adjusted_cost,0);
3295 h_rule_in.old_raf := nvl(p_asset_fin_rec_old.rate_adjustment_factor,1);
3296 h_rule_in.old_formula_factor := nvl(p_asset_fin_rec_old.formula_factor,1);
3297
3298 h_rule_in.transaction_header_id := p_trans_rec.transaction_header_id;
3299 h_rule_in.member_transaction_header_id := p_trans_rec.member_transaction_header_id;
3300 h_rule_in.transaction_date_entered := p_trans_rec.transaction_date_entered;
3301 h_rule_in.amortization_start_date := p_trans_rec.amortization_start_date;
3302 h_rule_in.adj_transaction_header_id := nvl(p_trans_rec_adj.transaction_header_id,
3303 p_trans_rec.transaction_header_id);
3304 h_rule_in.adj_mem_transaction_header_id := nvl(p_trans_rec_adj.member_transaction_header_id,
3305 p_trans_rec.member_transaction_header_id);
3306 h_rule_in.adj_transaction_date_entered := nvl(p_trans_rec_adj.transaction_date_entered,
3307 p_trans_rec.transaction_date_entered);
3308 h_rule_in.period_counter := p_period_rec.period_counter;
3309 h_rule_in.fiscal_year := p_period_rec.fiscal_year;
3310 h_rule_in.period_num := nvl(p_period_num,p_period_rec.period_num);
3311 h_rule_in.proceeds_of_sale := nvl(p_asset_retire_rec.proceeds_of_sale,0);
3312 h_rule_in.cost_of_removal := nvl(p_asset_retire_rec.cost_of_removal,0);
3313 h_rule_in.nbv_retired := nvl(p_asset_retire_rec.detail_info.nbv_retired,0);
3314 h_rule_in.reduction_rate := p_asset_fin_rec_new.reduction_rate;
3315 h_rule_in.eofy_reserve := nvl(p_asset_fin_rec_new.eofy_reserve,0);
3316 h_rule_in.adj_reserve := nvl(p_adj_reserve,0);
3317 h_rule_in.reserve_retired := nvl(p_reserve_retired,0);
3318 h_rule_in.recognize_gain_loss := p_asset_fin_rec_new.recognize_gain_loss;
3319 h_rule_in.tracking_method := p_asset_fin_rec_new.tracking_method;
3320 h_rule_in.allocate_to_fully_rsv_flag := p_asset_fin_rec_new.allocate_to_fully_rsv_flag;
3321 h_rule_in.allocate_to_fully_ret_flag := p_asset_fin_rec_new.allocate_to_fully_ret_flag;
3322 h_rule_in.excess_allocation_option := p_asset_fin_rec_new.excess_allocation_option;
3323 h_rule_in.depreciation_option := p_asset_fin_rec_new.depreciation_option;
3324 h_rule_in.member_rollup_flag := p_asset_fin_rec_new.member_rollup_flag;
3325 h_rule_in.unplanned_amount := nvl(p_unplanned_deprn_rec.unplanned_amount,0);
3326 h_rule_in.eofy_recoverable_cost := nvl(p_eofy_recoverable_cost,0);
3327 h_rule_in.eop_recoverable_cost := nvl(p_eop_recoverable_cost,0);
3328 h_rule_in.eofy_salvage_value := nvl(p_eofy_salvage_value,0);
3329 h_rule_in.eop_salvage_value := nvl(p_eop_salvage_value,0);
3330 h_rule_in.used_by_adjustment := p_used_by_adjustment;
3331 h_rule_in.eofy_flag := p_eofy_flag;
3332 h_rule_in.apply_reduction_flag := p_apply_reduction_flag;
3333 -- Bug4169773:
3334 h_rule_in.short_fy_flag := nvl(p_asset_fin_rec_new.short_fiscal_year_flag, 'NO');
3335 h_rule_in.mrc_sob_type_code := p_mrc_sob_type_code;
3336
3337 h_rule_out.new_deprn_rounding_flag := p_dpr.deprn_rounding_flag;
3338 ----------------------------------------------
3339 --- Set variables for each event types
3340 ----------------------------------------------
3341 if p_event_type ='ADDITION' then
3342
3343 h_rule_in.old_adjusted_cost := nvl(px_new_adjusted_cost,0);
3344 h_rule_in.old_raf := nvl(px_new_raf,1);
3345 h_rule_in.old_formula_factor := nvl(px_new_formula_factor,1);
3346
3347 elsif p_event_type = 'EXPENSED_ADJ' then
3348 h_rule_in.recoverable_cost := nvl(p_recoverable_cost,0);
3349 h_rule_in.adjusted_cost := nvl(p_asset_fin_rec_new.adjusted_cost,0);
3350 h_rule_in.current_total_rsv := nvl(p_current_total_rsv,0);
3351 h_rule_in.current_rsv := nvl(p_current_rsv,0);
3352 h_rule_in.current_total_ytd := nvl(p_current_total_ytd,0);
3353 h_rule_in.current_ytd := nvl(p_current_ytd,0);
3354 h_rule_in.hyp_basis := nvl(p_hyp_basis,0);
3355 h_rule_in.hyp_total_rsv := nvl(p_hyp_total_rsv,0);
3356 h_rule_in.hyp_rsv := nvl(p_hyp_rsv,0);
3357 h_rule_in.hyp_total_ytd := nvl(p_hyp_total_ytd,0);
3358 h_rule_in.hyp_ytd := nvl(p_hyp_ytd,0);
3359 h_rule_in.old_raf := nvl(px_new_raf,1);
3360 h_rule_in.old_formula_factor := nvl(px_new_formula_factor,1);
3361
3362 elsif p_event_type in ('AMORT_ADJ','AMORT_ADJ2','AMORT_ADJ3') then
3363 h_rule_in.adjusted_cost := nvl(p_adjusted_cost,0);
3364 h_rule_in.current_total_rsv := nvl(p_current_total_rsv,0);
3365 h_rule_in.current_rsv := nvl(p_current_rsv,0);
3366 h_rule_in.current_total_ytd := nvl(p_current_total_ytd,0);
3367 h_rule_in.current_ytd := nvl(p_current_ytd,0);
3368 h_rule_in.hyp_basis := nvl(p_hyp_basis,0);
3369 h_rule_in.hyp_total_rsv := nvl(p_hyp_total_rsv,0);
3370 h_rule_in.hyp_rsv := nvl(p_hyp_rsv,0);
3371
3372 -- Japan Tax Phase3
3373 if (nvl(p_trans_rec_adj.transaction_key,'X') = 'ES') then
3374 h_rule_in.transaction_flag := p_trans_rec_adj.transaction_key;
3375 end if;
3376
3377 elsif p_event_type ='RETIREMENT' then
3378 h_rule_in.adjustment_amount := nvl(p_asset_retire_rec.cost_retired,0);
3379 h_rule_in.recoverable_cost := nvl(p_recoverable_cost,0);
3380 h_rule_in.salvage_value := nvl(p_salvage_value,0);
3381
3382 -- Japan Tax Phase3
3383 if (nvl(p_asset_fin_rec_new.extended_deprn_flag,'N') = 'Y') then
3384 h_rule_in.transaction_flag := 'ES';
3385 end if;
3386
3387 elsif p_event_type ='AFTER_DEPRN' then
3388 h_rule_in.asset_id := p_dpr.asset_id;
3389 h_rule_in.book_type_code := p_dpr.book;
3390 h_rule_in.method_code := p_dpr.method_code;
3391 h_rule_in.life_in_months := p_dpr.life;
3392 h_rule_in.salvage_value := nvl(p_dpr.salvage_value,0);
3393 h_rule_in.recoverable_cost := nvl(p_dpr.rec_cost,0);
3394 h_rule_in.current_total_rsv := nvl(p_current_total_rsv,0);
3395 h_rule_in.current_rsv := nvl(p_current_rsv,0);
3396 h_rule_in.current_total_ytd := nvl(p_current_total_ytd,0);
3397 h_rule_in.old_adjusted_cost := nvl(px_new_adjusted_cost,0);
3398 h_rule_in.old_raf := nvl(p_dpr.rate_adj_factor,1);
3399 h_rule_in.old_formula_factor := nvl(p_dpr.formula_factor,1);
3400 h_rule_in.fiscal_year := p_fiscal_year;
3401 h_rule_in.period_num := p_period_num;
3402 h_rule_in.period_counter := p_period_counter;
3403 h_rule_in.eofy_reserve := p_eofy_reserve;
3404 h_rule_in.tracking_method := p_dpr.tracking_method;
3405 h_rule_in.allocate_to_fully_rsv_flag := p_dpr.allocate_to_fully_rsv_flag;
3406 h_rule_in.allocate_to_fully_ret_flag := p_dpr.allocate_to_fully_ret_flag;
3407 h_rule_in.excess_allocation_option := p_dpr.excess_allocation_option;
3408 h_rule_in.depreciation_option := p_dpr.depreciation_option;
3409 h_rule_in.member_rollup_flag := p_dpr.member_rollup_flag;
3410
3411 -- Treate asset type
3412 --
3413 -- Bug3017395: Make sure the existence of asset id
3414 -- If not, assume it is CAPITALIZED
3415 if h_rule_in.asset_type is null and
3416 nvl(h_rule_in.asset_id, 0) <> 0 then
3417
3418
3419 select ah.asset_type
3420 into h_rule_in.asset_type
3421 from fa_asset_history ah
3422 where h_rule_in.asset_id = ah.asset_id
3423 and ah.date_ineffective is null;
3424
3425 elsif h_rule_in.asset_type is null then
3426 h_rule_in.asset_type := 'CAPITALIZED';
3427 end if;
3428
3429 elsif p_event_type ='DEPRECIATE_FLAG_ADJ' then
3430 null;
3431 elsif p_event_type ='UNPLANNED_ADJ' then
3432 -- null;
3433 --bug fix 3590003
3434 h_rule_in.old_raf := px_new_raf;
3435 end if; -- Set varialbes for each event type
3436
3437
3438 --
3439 -- eofy and eop amounts are necessary only if deprn basis rules are following
3440 -- need eop amounts: 'PERIOD END AVERAGE', 'BEGINNING PERIOD'
3441 -- need eofy amounts: 'YEAR TO DATE AVERAGE', 'YEAR END BALANCE WITH HALF YEAR RULE'
3442 --
3443 if (fa_cache_pkg.fazcdbr_record.rule_name in ('PERIOD END AVERAGE', 'BEGINNING PERIOD',
3444 'YEAR TO DATE AVERAGE',
3445 'YEAR END BALANCE WITH HALF YEAR RULE',
3446 'YEAR TO DATE AVERAGE WITH HALF YEAR RULE')) then
3447 fa_debug_pkg.add(l_calling_fn, 'eofy_rec', p_eofy_recoverable_cost
3448 ,p_log_level_rec => p_log_level_rec);
3449 fa_debug_pkg.add(l_calling_fn, 'eop_rec', p_eop_recoverable_cost
3450 ,p_log_level_rec => p_log_level_rec);
3451 fa_debug_pkg.add(l_calling_fn, 'eofy_sal', p_eofy_salvage_value
3452 ,p_log_level_rec => p_log_level_rec);
3453 fa_debug_pkg.add(l_calling_fn, 'eop_sal', p_eop_salvage_value
3454 ,p_log_level_rec => p_log_level_rec);
3455 ---------------------------------------------
3456 -- Get end of fiscal year
3457 -- and end of period recoverable cost
3458 -- and salvage value
3459 ---------------------------------------------
3460 -- Get eofy recoverable cost and salvage value
3461 if p_eofy_recoverable_cost is null or
3462 p_eofy_salvage_value is null or
3463 p_eop_recoverable_cost is null or
3464 p_eop_salvage_value is null then
3465
3466 if (not FA_CALC_DEPRN_BASIS1_PKG.GET_EOFY_EOP
3467 (
3468 p_asset_id => h_rule_in.asset_id,
3469 p_book_type_code => h_rule_in.book_type_code,
3470 p_fiscal_year => h_rule_in.fiscal_year,
3471 p_period_num => h_rule_in.period_num,
3472 p_asset_type => h_rule_in.asset_type,
3473 p_recoverable_cost => h_rule_in.recoverable_cost,
3474 p_salvage_value => h_rule_in.salvage_value,
3475 p_mrc_sob_type_code => h_rule_in.mrc_sob_type_code,
3476 x_eofy_recoverable_cost => h_rule_in.eofy_recoverable_cost,
3477 x_eofy_salvage_value => h_rule_in.eofy_salvage_value,
3478 x_eop_recoverable_cost => h_rule_in.eop_recoverable_cost,
3479 x_eop_salvage_value => h_rule_in.eop_salvage_value
3480 ,p_log_level_rec => p_log_level_rec))
3481 then
3482 h_rule_in.eofy_recoverable_cost := nvl(p_eofy_recoverable_cost,0);
3483 h_rule_in.eofy_salvage_value := nvl(p_eofy_salvage_value,0);
3484 h_rule_in.eop_recoverable_cost := nvl(p_eop_recoverable_cost,0);
3485 h_rule_in.eop_salvage_value := nvl(p_eop_salvage_value,0);
3486 end if;
3487 end if; -- End of getting eofy and eop recoverable cost and salvage value
3488
3489 else
3490 h_rule_in.eofy_recoverable_cost := 0;
3491 h_rule_in.eofy_salvage_value := 0;
3492 h_rule_in.eop_recoverable_cost := 0;
3493 h_rule_in.eop_salvage_value := 0;
3494 end if; -- (fa_cache_pkg.fazcdbr_record.rule_name in (....
3495
3496 --------------------------------------------
3497 -- Set 0 to the end of fiscal year
3498 -- and end of period recoverable cost
3499 -- and salvage value if they returned 0
3500 --------------------------------------------
3501
3502 h_rule_in.eofy_recoverable_cost := nvl(h_rule_in.eofy_recoverable_cost,0);
3503 h_rule_in.eofy_salvage_value := nvl(h_rule_in.eofy_salvage_value,0);
3504 h_rule_in.eop_recoverable_cost := nvl(h_rule_in.eop_recoverable_cost,0);
3505 h_rule_in.eop_salvage_value := nvl(h_rule_in.eop_salvage_value,0);
3506
3507 ---------------------------------------------
3508 -- Call faxcdb (Calculate depreciable basis)
3509 -- function
3510 ---------------------------------------------
3511 if (not FA_CALC_DEPRN_BASIS1_PKG.faxcdb(h_rule_in,
3512 h_rule_out
3513 ,p_log_level_rec => p_log_level_rec)) then
3514 raise call_deprn_basis_err;
3515
3516 end if;
3517
3518 if p_dpr.deprn_rounding_flag is not null and h_rule_out.new_deprn_rounding_flag is null then
3519 h_rule_out.new_deprn_rounding_flag := p_dpr.deprn_rounding_flag;
3520 end if;
3521
3522 if p_log_level_rec.statement_level then
3523 fa_debug_pkg.add(fname=>'CALL_DEPRN_BASIS',
3524 element=>'h_rule_out.new_deprn_rounding_flag',
3525 value=> h_rule_out.new_deprn_rounding_flag
3526 ,p_log_level_rec => p_log_level_rec);
3527 end if;
3528
3529 px_new_adjusted_cost := h_rule_out.new_adjusted_cost;
3530 px_new_raf := h_rule_out.new_raf;
3531 px_new_formula_factor := h_rule_out.new_formula_factor;
3532 x_annual_deprn_rounding_flag := h_rule_out.new_deprn_rounding_flag;
3533
3534 return true;
3535
3536 EXCEPTION
3537 When call_deprn_basis_err then
3538 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3539 ,p_log_level_rec => p_log_level_rec);
3540 return (false);
3541
3542 WHEN OTHERS THEN
3543 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3544 ,p_log_level_rec => p_log_level_rec);
3545 return (false);
3546
3547 END CALL_DEPRN_BASIS;
3548
3549 --------------------------------------------------------
3550 -- Function: CALL_DEPRN_BASIS
3551 --
3552 -- This function is the cover function to call faxcdb
3553 -- from Transaction API and depreciation Engine
3554 --------------------------------------------------------
3555
3556 FUNCTION CALL_DEPRN_BASIS(
3557 p_event_type IN varchar2,
3558 p_asset_fin_rec_new IN fa_api_types.asset_fin_rec_type,
3559 p_asset_fin_rec_old IN fa_api_types.asset_fin_rec_type,
3560 p_asset_hdr_rec IN fa_api_types.asset_hdr_rec_type,
3561 p_asset_type_rec IN fa_api_types.asset_type_rec_type,
3562 p_asset_deprn_rec IN fa_api_types.asset_deprn_rec_type,
3563 p_trans_rec IN fa_api_types.trans_rec_type,
3564 p_trans_rec_adj IN fa_api_types.trans_rec_type,
3565 p_period_rec IN fa_api_types.period_rec_type,
3566 p_asset_retire_rec IN fa_api_types.asset_retire_rec_type,
3567 p_unplanned_deprn_rec IN fa_api_types.unplanned_deprn_rec_type,
3568 p_dpr IN fa_std_types.dpr_struct,
3569 p_fiscal_year IN number,
3570 p_period_num IN number,
3571 p_period_counter IN number,
3572 p_recoverable_cost IN number,
3573 p_salvage_value IN number,
3574 p_adjusted_cost IN number,
3575 p_current_total_rsv IN number,
3576 p_current_rsv IN number,
3577 p_current_total_ytd IN number,
3578 p_current_ytd IN number,
3579 p_hyp_basis IN number,
3580 p_hyp_total_rsv IN number,
3581 p_hyp_rsv IN number,
3582 p_hyp_total_ytd IN number,
3583 p_hyp_ytd IN number,
3584 p_eofy_recoverable_cost IN number,
3585 p_eop_recoverable_cost IN number,
3586 p_eofy_salvage_value IN number,
3587 p_eop_salvage_value IN number,
3588 p_eofy_reserve IN number,
3589 p_adj_reserve IN number,
3590 p_reserve_retired IN number,
3591 p_used_by_adjustment IN varchar2,
3592 p_eofy_flag IN varchar2,
3593 p_apply_reduction_flag IN varchar2,
3594 p_mrc_sob_type_code IN varchar2,
3595 px_new_adjusted_cost IN OUT NOCOPY number,
3596 px_new_raf IN OUT NOCOPY number,
3597 px_new_formula_factor IN OUT NOCOPY number,
3598 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return BOOLEAN IS
3599
3600 l_calling_fn varchar2(50) := 'fa_calc_deprn_basis1_pkg.CALL_DEPRN_BASIS';
3601 l_annual_deprn_rounding_flag varchar2(5);
3602
3603 call_deprn_basis_err EXCEPTION;
3604 BEGIN
3605
3606 if not CALL_DEPRN_BASIS(
3607 p_event_type => p_event_type,
3608 p_asset_fin_rec_new => p_asset_fin_rec_new,
3609 p_asset_fin_rec_old => p_asset_fin_rec_old,
3610 p_asset_hdr_rec => p_asset_hdr_rec,
3611 p_asset_type_rec => p_asset_type_rec,
3612 p_asset_deprn_rec => p_asset_deprn_rec,
3613 p_trans_rec => p_trans_rec,
3614 p_trans_rec_adj => p_trans_rec_adj,
3615 p_period_rec => p_period_rec,
3616 p_asset_retire_rec => p_asset_retire_rec,
3617 p_unplanned_deprn_rec => p_unplanned_deprn_rec,
3618 p_dpr => p_dpr,
3619 p_fiscal_year => p_fiscal_year,
3620 p_period_num => p_period_num,
3621 p_period_counter => p_period_counter,
3622 p_recoverable_cost => p_recoverable_cost,
3623 p_salvage_value => p_salvage_value,
3624 p_adjusted_cost => p_adjusted_cost,
3625 p_current_total_rsv => p_current_total_rsv,
3626 p_current_rsv => p_current_rsv,
3627 p_current_total_ytd => p_current_total_ytd,
3628 p_current_ytd => p_current_ytd,
3629 p_hyp_basis => p_hyp_basis,
3630 p_hyp_total_rsv => p_hyp_total_rsv,
3631 p_hyp_rsv => p_hyp_rsv,
3632 p_hyp_total_ytd => p_hyp_total_ytd,
3633 p_hyp_ytd => p_hyp_ytd,
3634 p_eofy_recoverable_cost => p_eofy_recoverable_cost,
3635 p_eop_recoverable_cost => p_eop_recoverable_cost,
3636 p_eofy_salvage_value => p_eofy_salvage_value,
3637 p_eop_salvage_value => p_eop_salvage_value,
3638 p_eofy_reserve => p_eofy_reserve,
3639 p_adj_reserve => p_adj_reserve,
3640 p_reserve_retired => p_reserve_retired,
3641 p_used_by_adjustment => p_used_by_adjustment,
3642 p_eofy_flag => p_eofy_flag,
3643 p_apply_reduction_flag => p_apply_reduction_flag,
3644 p_mrc_sob_type_code => p_mrc_sob_type_code,
3645 px_new_adjusted_cost => px_new_adjusted_cost,
3646 px_new_raf => px_new_raf,
3647 px_new_formula_factor => px_new_formula_factor,
3648 x_annual_deprn_rounding_flag => l_annual_deprn_rounding_flag
3649 ,p_log_level_rec => p_log_level_rec) then
3650 raise call_deprn_basis_err;
3651 end if;
3652
3653 return(true);
3654
3655 EXCEPTION
3656 When call_deprn_basis_err then
3657 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3658 ,p_log_level_rec => p_log_level_rec);
3659 return (false);
3660
3661 WHEN OTHERS THEN
3662 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3663 ,p_log_level_rec => p_log_level_rec);
3664 return (false);
3665
3666 END CALL_DEPRN_BASIS;
3667
3668 ------------------------------------------------------------
3669 -- Function: CALC_PROCEEDS
3670 --
3671 -- This function is to calculate Year-to-Date Proceeds
3672 -- and Life-to Date Proceeds of Do not Recognized Gain/Loss
3673 --
3674 -- p_asset_id : Asset Id
3675 -- p_asset_type : Asset Type
3676 -- p_book_type_code : Book Type Code
3677 -- p_period_counter : Period Counter
3678 -- p_mrc_sob_type_code : MRC SOB TYPE Code
3679 -- x_ltd_proceeds : Life-to Date Proceeds
3680 -- x_ytd_proceeds : Year-to-Date Proceeds
3681 --
3682 ------------------------------------------------------------
3683
3684 Function CALC_PROCEEDS (
3685 p_asset_id IN NUMBER,
3686 p_asset_type IN VARCHAR2,
3687 p_book_type_code IN VARCHAR2,
3688 p_period_counter IN NUMBER,
3689 p_mrc_sob_type_code IN VARCHAR2,
3690 x_ltd_proceeds OUT NOCOPY NUMBER,
3691 x_ytd_proceeds OUT NOCOPY NUMBER,
3692 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return BOOLEAN is
3693
3694 l_fy_start_date DATE; -- Start date of Fiscal Year
3695 l_period_end_date DATE; -- Close date at Transaction Period
3696
3697 -- Query end date of period
3698 cursor C_GET_DATE is
3699 select fy.start_date fy_start_date,
3700 dp.calendar_period_close_date cp_end_date
3701 from FA_FISCAL_YEAR fy,
3702 FA_DEPRN_PERIODS dp,
3703 FA_BOOK_CONTROLS bc
3704 where bc.book_type_code = dp.book_type_code
3705 and fy.fiscal_year = dp.fiscal_year
3706 and bc.fiscal_year_name = fy.fiscal_year_name
3707 and dp.book_type_code= p_book_type_code
3708 and dp.period_counter = p_period_counter;
3709
3710 cursor C_GET_DATE_M is
3711 select fy.start_date fy_start_date,
3712 dp.calendar_period_close_date cp_end_date
3713 from FA_FISCAL_YEAR fy,
3714 FA_DEPRN_PERIODS_MRC_V dp,
3715 FA_BOOK_CONTROLS_MRC_V bc
3716 where bc.book_type_code = dp.book_type_code
3717 and fy.fiscal_year = dp.fiscal_year
3718 and bc.fiscal_year_name = fy.fiscal_year_name
3719 and dp.book_type_code= p_book_type_code
3720 and dp.period_counter = p_period_counter;
3721
3722 -- Get LTD proceeds
3723 cursor C_LTD_PROCEEDS (
3724 p_period_end_date date
3725 ) is
3726 select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3727 from FA_RETIREMENTS ret,
3728 FA_TRANSACTION_HEADERS th
3729 where th.asset_id = p_asset_id
3730 and th.book_type_code = p_book_type_code
3731 and ret.status in ('PROCESSED','PENDING')
3732 and ret.transaction_header_id_in = th.transaction_header_id
3733 and th.transaction_date_entered <= p_period_end_date;
3734
3735 -- For Group Asset
3736 cursor GP_LTD_PROCEEDS (
3737 p_period_end_date date
3738 ) is
3739 select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3740 from FA_RETIREMENTS ret,
3741 FA_TRANSACTION_HEADERS th
3742 where th.asset_id = p_asset_id
3743 and th.book_type_code = p_book_type_code
3744 and ret.status in ('PROCESSED','PENDING')
3745 and ret.transaction_header_id_in = th.member_transaction_header_id
3746 and th.transaction_date_entered <= p_period_end_date;
3747
3748 cursor C_LTD_PROCEEDS_M (
3749 p_period_end_date date
3750 ) is
3751 select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3752 from FA_RETIREMENTS_MRC_V ret,
3753 FA_TRANSACTION_HEADERS th
3754 where th.asset_id = p_asset_id
3755 and th.book_type_code = p_book_type_code
3756 and ret.status in ('PROCESSED','PENDING')
3757 and ret.transaction_header_id_in = th.transaction_header_id
3758 and th.transaction_date_entered <= p_period_end_date;
3759
3760 -- For Group Asset
3761 cursor GP_LTD_PROCEEDS_M (
3762 p_period_end_date date
3763 ) is
3764 select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3765 from FA_RETIREMENTS_MRC_V ret,
3766 FA_TRANSACTION_HEADERS th
3767 where th.asset_id = p_asset_id
3768 and th.book_type_code = p_book_type_code
3769 and ret.status in ('PROCESSED','PENDING')
3770 and ret.transaction_header_id_in = th.member_transaction_header_id
3771 and th.transaction_date_entered <= p_period_end_date;
3772
3773 -- Get YTD proceeds
3774 cursor C_YTD_PROCEEDS (
3775 p_fy_start_date date,
3776 p_period_end_date date
3777 ) is
3778 select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3779 from FA_RETIREMENTS ret,
3780 FA_TRANSACTION_HEADERS th
3781 where th.asset_id = p_asset_id
3782 and th.book_type_code = p_book_type_code
3783 and ret.status in ('PROCESSED','PENDING')
3784 and ret.transaction_header_id_in = th.transaction_header_id
3785 and th.transaction_date_entered >= p_fy_start_date
3786 and th.transaction_date_entered <= p_period_end_date;
3787
3788 -- For Group Asset
3789 cursor GP_YTD_PROCEEDS (
3790 p_fy_start_date date,
3791 p_period_end_date date
3792 ) is
3793 select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3794 from FA_RETIREMENTS ret,
3795 FA_TRANSACTION_HEADERS th
3796 where th.asset_id = p_asset_id
3797 and th.book_type_code = p_book_type_code
3798 and ret.status in ('PROCESSED','PENDING')
3799 and ret.transaction_header_id_in = th.member_transaction_header_id
3800 and th.transaction_date_entered >= p_fy_start_date
3801 and th.transaction_date_entered <= p_period_end_date;
3802
3803 cursor C_YTD_PROCEEDS_M (
3804 p_fy_start_date date,
3805 p_period_end_date date
3806 ) is
3807 select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3808 from FA_RETIREMENTS_MRC_V ret,
3809 FA_TRANSACTION_HEADERS th
3810 where th.asset_id = p_asset_id
3811 and th.book_type_code = p_book_type_code
3812 and ret.status in ('PROCESSED','PENDING')
3813 and ret.transaction_header_id_in = th.transaction_header_id
3814 and th.transaction_date_entered >= p_fy_start_date
3815 and th.transaction_date_entered <= p_period_end_date;
3816
3817 -- For Group Asset
3818 cursor GP_YTD_PROCEEDS_M (
3819 p_fy_start_date date,
3820 p_period_end_date date
3821 ) is
3822 select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3823 from FA_RETIREMENTS_MRC_V ret,
3824 FA_TRANSACTION_HEADERS th
3825 where th.asset_id = p_asset_id
3826 and th.book_type_code = p_book_type_code
3827 and ret.status in ('PROCESSED','PENDING')
3828 and ret.transaction_header_id_in = th.member_transaction_header_id
3829 and th.transaction_date_entered >= p_fy_start_date
3830 and th.transaction_date_entered <= p_period_end_date;
3831
3832 l_calling_fn varchar2(50) := 'fa_calc_deprn_basis1_pkg.CALC_PROCEEDS';
3833
3834 Begin
3835
3836 -- Initialization
3837 x_ltd_proceeds :=0;
3838 x_ytd_proceeds :=0;
3839
3840 if p_mrc_sob_type_code <>'R' then -- Non MRC
3841
3842 -- Get start date of fiscal year and end date of period
3843 OPEN C_GET_DATE;
3844 FETCH C_GET_DATE into l_fy_start_date, l_period_end_date;
3845 CLOSE C_GET_DATE;
3846
3847 if p_asset_type='GROUP' then
3848 -- Calcluate LTD proceeds
3849 OPEN GP_LTD_PROCEEDS (l_period_end_date);
3850 FETCH GP_LTD_PROCEEDS into x_ltd_proceeds;
3851 CLOSE GP_LTD_PROCEEDS;
3852
3853 -- Calcluate YTD proceeds
3854 OPEN GP_YTD_PROCEEDS (l_fy_start_date,l_period_end_date);
3855 FETCH GP_YTD_PROCEEDS into x_ytd_proceeds;
3856 CLOSE GP_YTD_PROCEEDS;
3857
3858 else
3859 -- Calcluate LTD proceeds
3860 OPEN C_LTD_PROCEEDS (l_period_end_date);
3861 FETCH C_LTD_PROCEEDS into x_ltd_proceeds;
3862 CLOSE C_LTD_PROCEEDS;
3863
3864 -- Calcluate YTD proceeds
3865 OPEN C_YTD_PROCEEDS (l_fy_start_date,l_period_end_date);
3866 FETCH C_YTD_PROCEEDS into x_ytd_proceeds;
3867 CLOSE C_YTD_PROCEEDS;
3868 end if; -- End of GROUP
3869
3870 else -- MRC
3871
3872 -- Get start date of fiscal year and end date of period
3873 OPEN C_GET_DATE_M;
3874 FETCH C_GET_DATE_M into l_fy_start_date, l_period_end_date;
3875 CLOSE C_GET_DATE_M;
3876
3877 if p_asset_type='GROUP' then
3878 -- Calcluate LTD proceeds
3879 OPEN GP_LTD_PROCEEDS_M (l_period_end_date);
3880 FETCH GP_LTD_PROCEEDS_M into x_ltd_proceeds;
3881 CLOSE GP_LTD_PROCEEDS_M;
3882
3883 -- Calcluate YTD proceeds
3884 OPEN GP_YTD_PROCEEDS_M (l_fy_start_date,l_period_end_date);
3885 FETCH GP_YTD_PROCEEDS_M into x_ytd_proceeds;
3886 CLOSE GP_YTD_PROCEEDS_M;
3887
3888 else
3889 -- Calcluate LTD proceeds
3890 OPEN C_LTD_PROCEEDS_M (l_period_end_date);
3891 FETCH C_LTD_PROCEEDS_M into x_ltd_proceeds;
3892 CLOSE C_LTD_PROCEEDS_M;
3893
3894 -- Calcluate YTD proceeds
3895 OPEN C_YTD_PROCEEDS_M (l_fy_start_date,l_period_end_date);
3896 FETCH C_YTD_PROCEEDS_M into x_ytd_proceeds;
3897 CLOSE C_YTD_PROCEEDS_M;
3898 end if; -- End of GROUP
3899
3900 end if; -- End of MRC
3901
3902 if p_log_level_rec.statement_level then
3903 fa_debug_pkg.add(fname=>'CALC_PROCEEDS',
3904 element=>'x_ltd_proceeds',
3905 value=> x_ltd_proceeds
3906 ,p_log_level_rec => p_log_level_rec);
3907 fa_debug_pkg.add(fname=>'CALC_PROCEEDS',
3908 element=>'x_ytd_proceeds',
3909 value=> x_ytd_proceeds
3910 ,p_log_level_rec => p_log_level_rec);
3911 end if;
3912
3913
3914 return true;
3915
3916 EXCEPTION
3917
3918 WHEN OTHERS THEN
3919 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
3920 ,p_log_level_rec => p_log_level_rec);
3921 return (false);
3922
3923 END CALC_PROCEEDS;
3924
3925 -----------------------------------------------------------------
3926 -- Function: CALC_RETIRED_COST
3927 --
3928 -- This function calculate summary of retired cost.
3929 --
3930 -- p_event_type : Event Type
3931 -- p_asset_id : Asset ID
3932 -- p_asset_type : Asset Type
3933 -- p_book_type_code : Book Type Code
3934 -- p_fiscal_year : Fiscal year number
3935 -- p_period_num : Period number of fiscal year
3936 -- p_adjustment_amount : Retired cost at this transaction
3937 -- (Event type:RETIREMENT)
3938 -- p_ltd_ytd_flag : 'LTD' - Calculate Life to date
3939 -- Retired Cost.
3940 -- 'YTD' - Calculate Year to date
3941 -- Retired Cost.
3942 -- p_mrc_sob_type_code : MRC SOB type code
3943 -- x_retired_cost : Summary of retired cost
3944 --
3945 -----------------------------------------------------------------
3946
3947 FUNCTION CALC_RETIRED_COST (
3948 p_event_type IN VARCHAR2,
3949 p_asset_id IN NUMBER,
3950 p_asset_type IN VARCHAR2,
3951 p_book_type_code IN VARCHAR2,
3952 p_fiscal_year IN NUMBER,
3953 p_period_num IN NUMBER,
3954 p_adjustment_amount IN NUMBER,
3955 p_ltd_ytd_flag IN VARCHAR2,
3956 p_mrc_sob_type_code IN VARCHAR2,
3957 x_retired_cost OUT NOCOPY NUMBER,
3958 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return BOOLEAN is
3959
3960 l_fy_start_date date;
3961 l_period_end_date date;
3962
3963 -- Get period end date of transaction
3964 cursor C_PERIOD_END_DATE is
3965 select FY.START_DATE,
3966 CP.END_DATE
3967 from FA_CALENDAR_PERIODS CP,
3968 FA_CALENDAR_TYPES CT,
3969 FA_FISCAL_YEAR FY,
3970 FA_BOOK_CONTROLS BC
3971 where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
3972 and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
3973 and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
3974 and CP.END_DATE <= FY.END_DATE
3975 and CP.END_DATE >= FY.START_DATE
3976 and BC.BOOK_TYPE_CODE= p_book_type_code
3977 and FY.FISCAL_YEAR = p_fiscal_year
3978 and CP.PERIOD_NUM = p_period_num;
3979
3980 cursor C_PERIOD_END_DATE_M is
3981 select FY.START_DATE,
3982 CP.END_DATE
3983 from FA_CALENDAR_PERIODS CP,
3984 FA_CALENDAR_TYPES CT,
3985 FA_FISCAL_YEAR FY,
3986 FA_BOOK_CONTROLS_MRC_V BC
3987 where BC.DEPRN_CALENDAR = CP.CALENDAR_TYPE
3988 and CP.CALENDAR_TYPE = CT.CALENDAR_TYPE
3989 and CT.FISCAL_YEAR_NAME = FY.FISCAL_YEAR_NAME
3990 and CP.END_DATE <= FY.END_DATE
3991 and CP.END_DATE >= FY.START_DATE
3992 and BC.BOOK_TYPE_CODE= p_book_type_code
3993 and FY.FISCAL_YEAR = p_fiscal_year
3994 and CP.PERIOD_NUM = p_period_num;
3995
3996 -- Get summary of retired cost
3997
3998 cursor C_LTD_RETIRED_COST (
3999 p_period_end_date date
4000 ) is
4001 select nvl(sum(ret.cost_retired),0)
4002 from FA_RETIREMENTS ret,
4003 FA_TRANSACTION_HEADERS th
4004 where th.asset_id = p_asset_id
4005 and th.book_type_code = p_book_type_code
4006 and ret.status in ('PROCESSED','PENDING')
4007 and ret.transaction_header_id_in = th.transaction_header_id
4008 and th.transaction_date_entered <= p_period_end_date;
4009
4010 -- Cursor for LTD Retired Cost
4011
4012 cursor GP_LTD_RETIRED_COST (
4013 p_period_end_date date
4014 ) is
4015 select nvl(sum(ret.cost_retired),0)
4016 from FA_RETIREMENTS ret,
4017 FA_TRANSACTION_HEADERS th
4018 where th.asset_id = p_asset_id
4019 and th.book_type_code = p_book_type_code
4020 and ret.status in ('PROCESSED','PENDING')
4021 and ret.transaction_header_id_in = th.member_transaction_header_id
4022 and th.transaction_date_entered <= p_period_end_date;
4023
4024 cursor C_LTD_RETIRED_COST_M (
4025 p_period_end_date date
4026 ) is
4027 select nvl(sum(ret.cost_retired),0)
4028 from FA_RETIREMENTS_MRC_V ret,
4029 FA_TRANSACTION_HEADERS th
4030 where th.asset_id = p_asset_id
4031 and th.book_type_code = p_book_type_code
4032 and ret.status in ('PROCESSED','PENDING')
4033 and ret.transaction_header_id_in = th.transaction_header_id
4034 and th.transaction_date_entered <= p_period_end_date;
4035
4036 cursor GP_LTD_RETIRED_COST_M (
4037 p_period_end_date date
4038 ) is
4039 select nvl(sum(ret.cost_retired),0)
4040 from FA_RETIREMENTS_MRC_V ret,
4041 FA_TRANSACTION_HEADERS th
4042 where th.asset_id = p_asset_id
4043 and th.book_type_code = p_book_type_code
4044 and ret.status in ('PROCESSED','PENDING')
4045 and ret.transaction_header_id_in = th.member_transaction_header_id
4046 and th.transaction_date_entered <= p_period_end_date;
4047
4048 -- Cursor for YTD Retired Cost
4049 cursor C_YTD_RETIRED_COST (
4050 p_fy_start_date date,
4051 p_period_end_date date
4052 ) is
4053 select nvl(sum(ret.cost_retired),0)
4054 from FA_RETIREMENTS ret,
4055 FA_TRANSACTION_HEADERS th
4056 where th.asset_id = p_asset_id
4057 and th.book_type_code = p_book_type_code
4058 and ret.status in ('PROCESSED','PENDING')
4059 and ret.transaction_header_id_in = th.transaction_header_id
4060 and th.transaction_date_entered >= p_fy_start_date
4061 and th.transaction_date_entered <= p_period_end_date;
4062
4063
4064 cursor GP_YTD_RETIRED_COST (
4065 p_fy_start_date date,
4066 p_period_end_date date
4067 ) is
4068 select nvl(sum(ret.cost_retired),0)
4069 from FA_RETIREMENTS ret,
4070 FA_TRANSACTION_HEADERS th
4071 where th.asset_id = p_asset_id
4072 and th.book_type_code = p_book_type_code
4073 and ret.status in ('PROCESSED','PENDING')
4074 and ret.transaction_header_id_in = th.member_transaction_header_id
4075 and th.transaction_date_entered >= p_fy_start_date
4076 and th.transaction_date_entered <= p_period_end_date;
4077
4078 cursor C_YTD_RETIRED_COST_M (
4079 p_fy_start_date date,
4080 p_period_end_date date
4081 ) is
4082 select nvl(sum(ret.cost_retired),0)
4083 from FA_RETIREMENTS_MRC_V ret,
4084 FA_TRANSACTION_HEADERS th
4085 where th.asset_id = p_asset_id
4086 and th.book_type_code = p_book_type_code
4087 and ret.status in ('PROCESSED','PENDING')
4088 and ret.transaction_header_id_in = th.transaction_header_id
4089 and th.transaction_date_entered >= p_fy_start_date
4090 and th.transaction_date_entered <= p_period_end_date;
4091
4092 cursor GP_YTD_RETIRED_COST_M (
4093 p_fy_start_date date,
4094 p_period_end_date date
4095 ) is
4096 select nvl(sum(ret.cost_retired),0)
4097 from FA_RETIREMENTS_MRC_V ret,
4098 FA_TRANSACTION_HEADERS th
4099 where th.asset_id = p_asset_id
4100 and th.book_type_code = p_book_type_code
4101 and ret.status in ('PROCESSED','PENDING')
4102 and ret.transaction_header_id_in = th.member_transaction_header_id
4103 and th.transaction_date_entered >= p_fy_start_date
4104 and th.transaction_date_entered <= p_period_end_date;
4105
4106 l_calling_fn varchar2(50) := 'fa_calc_deprn_basis1_pkg.calc_retired_cost';
4107
4108
4109 begin
4110
4111 -- Initialization
4112 x_retired_cost := 0;
4113
4114 -- set fy_start_date and period_end_date
4115 if p_mrc_sob_type_code <>'R' then -- Not MRC
4116
4117 -- Calculate period end date
4118 OPEN C_PERIOD_END_DATE;
4119 FETCH C_PERIOD_END_DATE into l_fy_start_date,l_period_end_date;
4120 CLOSE C_PERIOD_END_DATE;
4121 else -- MRC
4122
4123 -- Calculate period end date
4124 OPEN C_PERIOD_END_DATE_M;
4125 FETCH C_PERIOD_END_DATE_M into l_fy_start_date, l_period_end_date;
4126 CLOSE C_PERIOD_END_DATE_M;
4127 end if; -- End of setting fy_start_date and period_end_date
4128
4129 if p_mrc_sob_type_code <>'R' then -- Not MRC
4130
4131 if p_ltd_ytd_flag ='LTD' then
4132 -- Calculate summary of retired cost
4133 if p_asset_type ='GROUP' then
4134 OPEN GP_LTD_RETIRED_COST (l_period_end_date);
4135 FETCH GP_LTD_RETIRED_COST into x_retired_cost;
4136 CLOSE GP_LTD_RETIRED_COST;
4137 else
4138 OPEN C_LTD_RETIRED_COST (l_period_end_date);
4139 FETCH C_LTD_RETIRED_COST into x_retired_cost;
4140 CLOSE C_LTD_RETIRED_COST;
4141 end if; -- End of Group
4142 else -- YTD
4143 -- Calculate summary of retired cost
4144 if p_asset_type ='GROUP' then
4145 OPEN GP_YTD_RETIRED_COST (l_fy_start_date,l_period_end_date);
4146 FETCH GP_YTD_RETIRED_COST into x_retired_cost;
4147 CLOSE GP_YTD_RETIRED_COST;
4148 else
4149 OPEN C_YTD_RETIRED_COST (l_fy_start_date,l_period_end_date);
4150 FETCH C_YTD_RETIRED_COST into x_retired_cost;
4151 CLOSE C_YTD_RETIRED_COST;
4152 end if; -- End of Group
4153 end if; -- End of LTD or YTD
4154
4155 else -- MRC
4156
4157 if p_ltd_ytd_flag ='LTD' then
4158 -- Calculate summary of retired cost
4159 if p_asset_type ='GROUP' then
4160 OPEN GP_LTD_RETIRED_COST_M (l_period_end_date);
4161 FETCH GP_LTD_RETIRED_COST_M into x_retired_cost;
4162 CLOSE GP_LTD_RETIRED_COST_M;
4163 else
4164 OPEN C_LTD_RETIRED_COST_M (l_period_end_date);
4165 FETCH C_LTD_RETIRED_COST_M into x_retired_cost;
4166 CLOSE C_LTD_RETIRED_COST_M;
4167 end if; -- End of Group
4168 else -- YTD
4169 -- Calculate summary of retired cost
4170 if p_asset_type ='GROUP' then
4171 OPEN GP_YTD_RETIRED_COST_M (l_fy_start_date,l_period_end_date);
4172 FETCH GP_YTD_RETIRED_COST_M into x_retired_cost;
4173 CLOSE GP_YTD_RETIRED_COST_M;
4174 else
4175 OPEN C_YTD_RETIRED_COST_M (l_fy_start_date,l_period_end_date);
4176 FETCH C_YTD_RETIRED_COST_M into x_retired_cost;
4177 CLOSE C_YTD_RETIRED_COST_M;
4178 end if; -- End of Group
4179 end if; -- End of LTD or YTD
4180
4181 end if; -- End of MRC
4182
4183 if p_event_type ='RETIREMENT'
4184 and fa_calc_deprn_basis1_pkg.g_rule_in.used_by_adjustment is null
4185 then
4186 x_retired_cost := nvl(x_retired_cost,0) + abs(nvl(p_adjustment_amount,0));
4187 end if;
4188
4189 if p_log_level_rec.statement_level then
4190 fa_debug_pkg.add(fname=>'CALC_RETIRED_COST',
4191 element=>'x_retired_cost',
4192 value=> x_retired_cost
4193 ,p_log_level_rec => p_log_level_rec);
4194 end if;
4195
4196 return true;
4197
4198 EXCEPTION
4199
4200 WHEN OTHERS THEN
4201 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
4202 ,p_log_level_rec => p_log_level_rec);
4203 return (false);
4204
4205 END CALC_RETIRED_COST;
4206
4207 ---------------------------------------------------------------------
4208 -- Function: GET_MEM_TRANS_INFO
4209 --
4210 -- This function is to get the transaction infomation of member
4211 --
4212 -- p_member_transaction_header_id :Transaction Header Id of member
4213 -- p_mrc_sob_type_code :MRC SOB Type Code
4214 -- x_member_transaction_type_code :Transaction Type Code of member
4215 -- x_member_proceeds :Proceeds - Cost of Removal at the transaction
4216 -- x_member_reduction_rate: Reduction_rate at the transaction
4217 --
4218 ---------------------------------------------------------------------
4219 Function GET_MEM_TRANS_INFO (
4220 p_member_transaction_header_id IN NUMBER,
4221 p_mrc_sob_type_code IN VARCHAR2,
4222 x_member_transaction_type_code OUT NOCOPY VARCHAR2,
4223 x_member_proceeds OUT NOCOPY NUMBER,
4224 x_member_reduction_rate OUT NOCOPY NUMBER,
4225 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
4226 return boolean is
4227
4228 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis1_pkg.get_mem_trans_info';
4229
4230 get_mem_trans_info EXCEPTION;
4231
4232 cursor C_GET_MEM_TRANS_HEADER is
4233 select TH.TRANSACTION_TYPE_CODE
4234 from FA_TRANSACTION_HEADERS TH
4235 where TH.TRANSACTION_HEADER_ID = p_member_transaction_header_id;
4236
4237
4238 -- Get Proceeds of member asset
4239 cursor C_GET_MEM_PROCEEDS is
4240 select nvl(RET.NBV_RETIRED,0)
4241 from FA_RETIREMENTS RET
4242 where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4243
4244 -- MRC
4245 cursor C_GET_MEM_PROCEEDS_M is
4246 select nvl(RET.NBV_RETIRED,0)
4247 from FA_RETIREMENTS_MRC_V RET
4248 where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4249
4250 -- Get reduction_rate of member asset
4251 cursor C_GET_REDUCITON_RATE is
4252 select BK.REDUCTION_RATE
4253 from FA_BOOKS BK
4254 where BK.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4255
4256 -- MRC
4257 cursor C_GET_REDUCITON_RATE_M is
4258 select BK.REDUCTION_RATE
4259 from FA_BOOKS_MRC_V BK
4260 where BK.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4261
4262 begin
4263
4264 -- Set Initialization
4265 x_member_transaction_type_code := null;
4266 x_member_proceeds :=0;
4267 x_member_reduction_rate := null;
4268
4269 if p_member_transaction_header_id is null then
4270
4271 return true;
4272
4273 else -- p_member_transaction_header_id is not null
4274
4275 OPEN C_GET_MEM_TRANS_HEADER;
4276 FETCH C_GET_MEM_TRANS_HEADER into x_member_transaction_type_code;
4277 CLOSE C_GET_MEM_TRANS_HEADER;
4278
4279 -- When the transaction is RETIREMENT,
4280 -- Get Proceeds - Cost of Removal.
4281
4282 if x_member_transaction_type_code like '%RETIREMENT' then
4283 if p_mrc_sob_type_code <>'R' then
4284
4285 OPEN C_GET_MEM_PROCEEDS;
4286 FETCH C_GET_MEM_PROCEEDS into x_member_proceeds;
4287 CLOSE C_GET_MEM_PROCEEDS;
4288 else
4289
4290 OPEN C_GET_MEM_PROCEEDS_M;
4291 FETCH C_GET_MEM_PROCEEDS_M into x_member_proceeds;
4292 CLOSE C_GET_MEM_PROCEEDS_M;
4293 end if;
4294 else
4295 x_member_proceeds := 0;
4296 end if; -- End of RETIREMENT
4297 end if; -- End of member_transaction_header_id is not null
4298
4299 -- Get Reduction Rate at the member transaction
4300 if p_mrc_sob_type_code <>'R' then
4301
4302 OPEN C_GET_REDUCITON_RATE;
4303 FETCH C_GET_REDUCITON_RATE into x_member_reduction_rate;
4304 CLOSE C_GET_REDUCITON_RATE;
4305
4306 else
4307
4308 OPEN C_GET_REDUCITON_RATE_M;
4309 FETCH C_GET_REDUCITON_RATE_M into x_member_reduction_rate;
4310 CLOSE C_GET_REDUCITON_RATE_M;
4311
4312 end if;
4313 -- End of Getting reduction rate
4314
4315 return true;
4316
4317 EXCEPTION
4318
4319 WHEN OTHERS THEN
4320 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
4321 ,p_log_level_rec => p_log_level_rec);
4322 return (false);
4323
4324 End;
4325
4326 ---------------------------------------------------------------------
4327 -- Function: SERVER_VALIDATION
4328 --
4329 -- This function is to validate unexpected values
4330 --
4331 ---------------------------------------------------------------------
4332
4333 Function SERVER_VALIDATION (p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
4334 return boolean is
4335
4336 svr_val_err exception;
4337 l_calling_fn VARCHAR2(50) := 'fa_calc_deprn_basis1_pkg.server_validation';
4338
4339 begin
4340
4341 -- Validate recognize_gain_loss='Y'
4342 if g_rule_in.recognize_gain_loss='Y'
4343 and fa_cache_pkg.fazcdbr_record.rule_name in
4344 ('YEAR END BALANCE','YEAR END BALANCE WITH POSITIVE REDUCTION AMOUNT',
4345 'YEAR END BALANCE WITH HALF YEAR RULE')
4346 then
4347 if g_log_level_rec.statement_level then
4348 fa_debug_pkg.add(fname=>'server_validation',
4349 element=>'recognize_gain_loss',
4350 value=> g_rule_in.recognize_gain_loss
4351 ,p_log_level_rec => p_log_level_rec);
4352 end if;
4353
4354 raise svr_val_err;
4355
4356 end if;
4357
4358 -- Validate recognize_gain_loss='N'
4359 if g_rule_in.recognize_gain_loss='N'
4360 and (fa_cache_pkg.fazcdbr_record.rule_name in
4361 ('FLAT RATE EXTENSION',
4362 'USE FISCAL YEAR BEGINNING BASIS')
4363 or (fa_cache_pkg.fazcdbr_record.rule_name = 'YEAR TO DATE AVERAGE'
4364 and g_rule_in.calc_basis = 'NBV')
4365 )
4366 then
4367 if g_log_level_rec.statement_level then
4368 fa_debug_pkg.add(fname=>'server_validation',
4369 element=>'recognize_gain_loss',
4370 value=> g_rule_in.recognize_gain_loss
4371 ,p_log_level_rec => p_log_level_rec);
4372 end if;
4373
4374 raise svr_val_err;
4375
4376 end if;
4377
4378 return true;
4379
4380 EXCEPTION
4381 WHEN svr_val_err THEN
4382 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
4383 ,p_log_level_rec => p_log_level_rec);
4384 return (false);
4385
4386 WHEN OTHERS THEN
4387 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
4388 ,p_log_level_rec => p_log_level_rec);
4389 return (false);
4390
4391 End;
4392
4393 end FA_CALC_DEPRN_BASIS1_PKG;