[Home] [Help]
PACKAGE BODY: APPS.FA_ADJUSTMENTS_T_PKG
Source
1 PACKAGE BODY FA_ADJUSTMENTS_T_PKG AS
2 /* $Header: fapadjtb.pls 120.1.12010000.8 2010/04/29 12:21:22 deemitta ship $ */
3
4 procedure prepare(w_clause in varchar2, p_batch_id in number, action_flag in varchar2) is
5
6 type refcrs is ref cursor;
7 type chartbltyp is table of varchar2(255);
8 type numtbltyp is table of number;
9 type datetbltyp is table of date;
10
11 l_asset_number chartbltyp;
12 l_asset_type chartbltyp;
13 l_attribute_category_code chartbltyp;
14 l_bonus_rule chartbltyp;
15 l_book_type_code chartbltyp;
16 l_ceiling_name chartbltyp;
17 l_depreciate_flag chartbltyp;
18 l_deprn_limit_type chartbltyp;
19 l_deprn_method_code chartbltyp;
20 l_description chartbltyp;
21 l_extended_deprn_flag chartbltyp;
22 l_period_name chartbltyp;
23 l_prorate_convention_code chartbltyp;
24 l_short_fiscal_year_flag chartbltyp;
25 l_transaction_name chartbltyp;
26 l_adjusted_rate numtbltyp;
27 l_allowed_deprn_limit numtbltyp;
28 l_allowed_deprn_limit_amount numtbltyp;
29 l_basic_rate numtbltyp;
30 l_cost numtbltyp;
31 l_deprn_reserve numtbltyp;
32 l_extended_depreciation_period numtbltyp;
33 l_fully_rsvd_revals_counter numtbltyp;
34 l_group_asset_id numtbltyp;
35 l_itc_amount_id numtbltyp;
36 l_life_in_months numtbltyp;
37 l_original_cost numtbltyp;
38 l_period_counter_fully_rsv numtbltyp;
39 l_production_capacity numtbltyp;
43 l_salvage_value numtbltyp;
40 l_reval_amortization_basis numtbltyp;
41 l_reval_ceiling numtbltyp;
42 l_reval_reserve numtbltyp;
44 l_unrevalued_cost numtbltyp;
45 l_ytd_deprn numtbltyp;
46 l_ytd_reval_deprn_expense numtbltyp;
47 l_batch_id numtbltyp;
48 l_amortization_start_date datetbltyp;
49 l_conversion_date datetbltyp;
50 l_date_placed_in_service datetbltyp;
51 l_original_deprn_start_date datetbltyp;
52 rc_extended_deprn refcrs;
53 l_query varchar2(5000);
54 l_calling_fn varchar2(50) := 'FA_ADJUSTMENTS_T_PKG.submit';
55 v_err_code number;
56 v_err_msg varchar2(255);
57
58 l_batch_size number;
59 l_pctr number;
60 l_name varchar2(15);
61 l_count number := 0;
62 l_imp_reserve numtbltyp; --phase5
63
64 cursor c_books is
65 select asset_number anum
66 , book_type_code book
67 , allowed_deprn_limit_amount limit_amt
68 from fa_adjustments_t
69 where extended_deprn_flag = 'Y'
70 and batch_id = p_batch_id
71 order by asset_number;
72
73 cursor all_books is
74 select asset_id aid
75 , fat.asset_number anum
76 , book_type_code book
77 from fa_adjustments_t fat
78 , fa_additions_b fab
79 where batch_id = p_batch_id
80 and fat.asset_number = fab.asset_number
81 order by book_type_code;
82
83
84 cursor set_period_name is
85 select fadp.period_name name
86 , fat.asset_number num
87 , fat.book_type_code book
88 from fa_deprn_periods fadp
89 , fa_adjustments_t fat
90 where fadp.period_counter = fat.period_counter_fully_reserved
91 and fadp.book_type_code = fat.book_type_code
92 and fat.batch_id = p_batch_id;
93
94 begin
95
96 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
97
98 l_query := 'select asset_number,
99 asset_type,
100 attribute_category_code,
101 bonus_rule,
102 book_type_code,
103 ceiling_name,
104 depreciate_flag,
105 deprn_limit_type,
106 deprn_method_code,
107 description,
108 extended_deprn_flag,
109 period_name,
110 prorate_convention_code,
111 short_fiscal_year_flag,
112 transaction_name,
113 adjusted_rate,
114 allowed_deprn_limit,
115 allowed_deprn_limit_amount,
116 basic_rate,
117 cost,
118 deprn_reserve,
119 extended_depreciation_period,
120 fully_rsvd_revals_counter,
121 group_asset_id,
122 itc_amount_id,
123 life_in_months,
124 original_cost,
125 period_counter_fully_reserved,
126 production_capacity,
127 reval_amortization_basis,
128 reval_ceiling,
129 reval_reserve,
130 salvage_value,
131 unrevalued_cost,
132 ytd_deprn,
133 ytd_reval_deprn_expense,
134 amortization_start_date,
135 conversion_date,
136 date_placed_in_service,
137 original_deprn_start_date,
138 impairment_reserve,
139 '||p_batch_id||'
140 from fa_extended_deprn_v
141 where '|| w_clause ||' ';
142
143
144 open rc_extended_deprn for l_query;
145 loop
146
147 fetch rc_extended_deprn bulk collect into
148 l_asset_number,
149 l_asset_type,
150 l_attribute_category_code,
151 l_bonus_rule,
152 l_book_type_code,
153 l_ceiling_name,
154 l_depreciate_flag,
155 l_deprn_limit_type,
156 l_deprn_method_code,
157 l_description,
158 l_extended_deprn_flag,
159 l_period_name,
160 l_prorate_convention_code,
161 l_short_fiscal_year_flag,
162 l_transaction_name,
163 l_adjusted_rate,
164 l_allowed_deprn_limit,
165 l_allowed_deprn_limit_amount,
166 l_basic_rate,
167 l_cost,
171 l_group_asset_id,
168 l_deprn_reserve,
169 l_extended_depreciation_period,
170 l_fully_rsvd_revals_counter,
172 l_itc_amount_id,
173 l_life_in_months,
174 l_original_cost,
175 l_period_counter_fully_rsv,
176 l_production_capacity,
177 l_reval_amortization_basis,
178 l_reval_ceiling,
179 l_reval_reserve,
180 l_salvage_value,
181 l_unrevalued_cost,
182 l_ytd_deprn,
183 l_ytd_reval_deprn_expense,
184 l_amortization_start_date,
185 l_conversion_date,
186 l_date_placed_in_service,
187 l_original_deprn_start_date,
188 l_imp_reserve,
189 l_batch_id LIMIT l_batch_size;
190
191 --#bug 7608247 Moved this check to exit above insert statement.
192 --Removed for bug 7632858
193 --exit when rc_extended_deprn%notfound;
194
195 --if rc_extended_deprn%rowcount > 0 then
196 /* Added for 7632858 */
197 if l_asset_number.count > 0 then
198
199 forall j in l_asset_number.first..l_asset_number.last
200 insert into fa_adjustments_t
201 (
202 asset_number,
203 asset_type,
204 attribute_category_code,
205 bonus_rule,
206 book_type_code,
207 ceiling_name,
208 depreciate_flag,
209 deprn_limit_type,
210 deprn_method_code,
211 description,
212 extended_deprn_flag,
213 period_name,
214 prorate_convention_code,
215 short_fiscal_year_flag,
216 transaction_name,
217 adjusted_rate,
218 allowed_deprn_limit,
219 allowed_deprn_limit_amount,
220 basic_rate,
221 cost,
222 deprn_reserve,
223 extended_depreciation_period,
224 fully_rsvd_revals_counter,
225 group_asset_id,
226 itc_amount_id,
227 life_in_months,
228 original_cost,
229 period_counter_fully_reserved,
230 production_capacity,
231 reval_amortization_basis,
232 reval_ceiling,
233 reval_reserve,
234 salvage_value,
235 unrevalued_cost,
236 ytd_deprn,
237 ytd_reval_deprn_expense,
238 amortization_start_date,
239 conversion_date,
240 date_placed_in_service,
241 original_deprn_start_date,
242 impairment_reserve,
243 batch_id,
244 request_id
245 )
246 values
250 l_attribute_category_code(j),
247 (
248 l_asset_number(j),
249 l_asset_type(j),
251 l_bonus_rule(j),
252 l_book_type_code(j),
253 l_ceiling_name(j),
254 l_depreciate_flag(j),
255 l_deprn_limit_type(j),
256 l_deprn_method_code(j),
257 l_description(j),
258 l_extended_deprn_flag(j),
259 l_period_name(j),
260 l_prorate_convention_code(j),
261 l_short_fiscal_year_flag(j),
262 l_transaction_name(j),
263 l_adjusted_rate(j),
264 l_allowed_deprn_limit(j),
265 l_allowed_deprn_limit_amount(j),
266 l_basic_rate(j),
267 l_cost(j),
268 l_deprn_reserve(j),
269 l_extended_depreciation_period(j),
270 l_fully_rsvd_revals_counter(j),
271 l_group_asset_id(j),
272 l_itc_amount_id(j),
273 l_life_in_months(j),
274 l_original_cost(j),
275 l_period_counter_fully_rsv(j),
276 l_production_capacity(j),
277 l_reval_amortization_basis(j),
278 l_reval_ceiling(j),
279 l_reval_reserve(j),
280 l_salvage_value(j),
281 l_unrevalued_cost(j),
282 l_ytd_deprn(j),
283 l_ytd_reval_deprn_expense(j),
284 l_amortization_start_date(j),
285 l_conversion_date(j),
286 l_date_placed_in_service(j),
287 l_original_deprn_start_date(j),
288 l_imp_reserve(j),
289 l_batch_id(j),
290 -1*l_batch_id(j));
291
292 end if;
293
294 commit work;
295
296 --Added for bug 7632858
297
298 exit when l_asset_number.count < l_batch_size;
299
300 --exit when rc_extended_deprn%notfound;
301
302 --#bug 7608247 Moved this check to exit above insert statement.
303
304 end loop;
305
306 close rc_extended_deprn;
307
308 -- set the value of extended_deprn_period_name for assets with extended_deprn_flag = 'Y'
309 if (action_flag = 'U')then -- taking cases when Y->N is done or Y->Y ro Y->U
310
311 for i in c_books loop
312 l_count := l_count + 1;
313
314 select cp.period_name name
315 into l_name
316 from fa_book_controls bc
317 , fa_fiscal_year fy
318 , fa_calendar_types ct
319 , fa_calendar_periods cp
320 , fa_adjustments_t fat
321 where bc.book_type_code = i.book
322 and fat.batch_id = p_batch_id
323 and fat.asset_number = i.anum
324 and bc.deprn_calendar = ct.calendar_type
325 and cp.calendar_type = ct.calendar_type
326 and bc.fiscal_year_name = ct.fiscal_year_name
327 and fy.fiscal_year_name = ct.fiscal_year_name
328 and cp.period_num = 1
329 and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
330 and fy.fiscal_year = (fat.extended_depreciation_period-1)/ct.number_per_fiscal_year
331 and cp.start_date = fy.start_date;
332
333 update fa_adjustments_t
334 set extended_deprn_period_name = l_name
335 , extended_deprn_limit = i.limit_amt
336 where book_type_code = i.book
337 and asset_number = i.anum
338 and batch_id = p_batch_id;
339
340 if( mod(l_count,l_batch_size) = 0 )then
341 commit work;
342 end if;
343
344 end loop;
345 end if;
346
347 -- set the period name of period when fully reserved
348 l_count := 0;
349
350 for i in set_period_name loop
351 l_count := l_count + 1;
352
353 update fa_adjustments_t
354 set period_name = i.name
355 where book_type_code = i.book
356 and asset_number = i.num
357 and batch_id = p_batch_id;
358
359 if( mod(l_count,l_batch_size) = 0 )then
360 commit work;
361 end if;
362
363 end loop;
364
365 l_count := 0;
366 if (action_flag = 'Y')then
367 -- default the value of extended_deprn_period_name to the first period in next fiscal year
368 for i in all_books loop
369
370 select cp.period_name name
371 , fy.fiscal_year*ct.number_per_fiscal_year + 1 pctr
372 into l_name
373 , l_pctr
374 from fa_books bks
375 , fa_book_controls bc
376 , fa_fiscal_year fy
377 , fa_calendar_types ct
378 , fa_calendar_periods cp
379 where bc.book_type_code = i.book
380 and bks.asset_id = i.aid
381 and bc.book_type_code = bks.book_type_code
382 and bc.deprn_calendar = ct.calendar_type
383 and cp.calendar_type = ct.calendar_type
384 and bc.fiscal_year_name = ct.fiscal_year_name
385 and fy.fiscal_year_name = ct.fiscal_year_name
386 and cp.period_num = 1
387 and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
388 and fy.fiscal_year = decode(sign(2007 -
389 decode(mod(bks.period_counter_fully_reserved,ct.number_per_fiscal_year)
390 , 0 , (bks.period_counter_fully_reserved-1)/ct.number_per_fiscal_year
391 , bks.period_counter_fully_reserved/ct.number_per_fiscal_year))
392 , 1, (select min(fiscal_year)
393 from fa_fiscal_year
394 where fiscal_year_name = ct.fiscal_year_name
395 and start_date >= to_date('01/04/2007', 'DD/MM/RRRR'))
396 /* Bug 7229538. The existing Hard coded year 2007 is removed.
397 Because it is not guarantee that fiscal year always starts from 01-Apr-2007.
398 In some cases the year might have started from 01-Jan-207.
399 In that case if you hardcode to 2007 This query never returns any row for those assets
400 which are already fully reserved before 01-Apr-2007. So changed to get the immediate fiscal year
401 which is after 31-Mar-2007. If the year starts from 01-Apr-2007 then it returns 2007 otherwise it returns 2008*/
402 , ceil((bks.period_counter_fully_reserved)/ct.number_per_fiscal_year))
403 and cp.start_date = fy.start_date
404 and bks.period_counter_fully_reserved is not null
405 and bks.transaction_header_id_out is null;
406
407
408 update fa_adjustments_t
409 set extended_deprn_period_name = l_name
410 , extended_depreciation_period = l_pctr
411 , posting_status = 'POST'
412 , extended_deprn_flag = 'Y'
413 , extended_deprn_limit = 1
414 where book_type_code = i.book
415 and asset_number = i.anum
416 and batch_id = p_batch_id;
417
418 if( mod(l_count,l_batch_size) = 0 )then
419 commit work;
420 end if;
421
422 l_count := l_count + 1;
423
424 end loop;
425
426 elsif (action_flag <> 'U') then -- flag U indicates no action to be taken, as Avail Extended Deprn was Undecided for Bulk operation
427 update fa_adjustments_t
428 set posting_status = 'POST'
429 , extended_deprn_flag = action_flag
430 where batch_id = p_batch_id;
431
432 commit work;
433
434 end if;
435
436 exception when others then
437 v_err_code := sqlcode;
438 v_err_msg := sqlerrm(sqlcode);
439
440 end;
441
442 END FA_ADJUSTMENTS_T_PKG;