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