[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.1 2008/07/28 13:22:10 appldev ship $ */
3 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
4
5 procedure prepare(w_clause in varchar2, p_batch_id in number, action_flag in varchar2) is
6
7 type refcrs is ref cursor;
8 type chartbltyp is table of varchar2(255);
9 type numtbltyp is table of number;
10 type datetbltyp is table of date;
11
12 l_asset_number chartbltyp;
13 l_asset_type chartbltyp;
14 l_attribute_category_code chartbltyp;
15 l_bonus_rule chartbltyp;
16 l_book_type_code chartbltyp;
17 l_ceiling_name chartbltyp;
18 l_depreciate_flag chartbltyp;
19 l_deprn_limit_type chartbltyp;
20 l_deprn_method_code chartbltyp;
21 l_description chartbltyp;
22 l_extended_deprn_flag chartbltyp;
23 l_period_name chartbltyp;
24 l_prorate_convention_code chartbltyp;
25 l_short_fiscal_year_flag chartbltyp;
26 l_transaction_name chartbltyp;
27 l_adjusted_rate numtbltyp;
28 l_allowed_deprn_limit numtbltyp;
29 l_allowed_deprn_limit_amount numtbltyp;
30 l_basic_rate numtbltyp;
31 l_cost numtbltyp;
32 l_deprn_reserve numtbltyp;
33 l_extended_depreciation_period numtbltyp;
34 l_fully_rsvd_revals_counter numtbltyp;
35 l_group_asset_id numtbltyp;
36 l_itc_amount_id numtbltyp;
37 l_life_in_months numtbltyp;
38 l_original_cost numtbltyp;
39 l_period_counter_fully_rsv numtbltyp;
40 l_production_capacity numtbltyp;
41 l_reval_amortization_basis numtbltyp;
42 l_reval_ceiling numtbltyp;
43 l_reval_reserve numtbltyp;
44 l_salvage_value numtbltyp;
45 l_unrevalued_cost numtbltyp;
46 l_ytd_deprn numtbltyp;
47 l_ytd_reval_deprn_expense numtbltyp;
48 l_batch_id numtbltyp;
49 l_amortization_start_date datetbltyp;
50 l_conversion_date datetbltyp;
51 l_date_placed_in_service datetbltyp;
52 l_original_deprn_start_date datetbltyp;
53 rc_extended_deprn refcrs;
54 l_query varchar2(5000);
55 l_calling_fn varchar2(50) := 'FA_ADJUSTMENTS_T_PKG.submit';
56 v_err_code number;
57 v_err_msg varchar2(255);
58
59 l_batch_size number;
60 l_pctr number;
61 l_name varchar2(15);
62 l_count number := 0;
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 '||p_batch_id||'
139 from fa_extended_deprn_v
140 where '|| w_clause ||' ';
141
142
143 open rc_extended_deprn for l_query;
144 loop
145
146 fetch rc_extended_deprn bulk collect into
147 l_asset_number,
148 l_asset_type,
149 l_attribute_category_code,
150 l_bonus_rule,
151 l_book_type_code,
152 l_ceiling_name,
153 l_depreciate_flag,
154 l_deprn_limit_type,
155 l_deprn_method_code,
156 l_description,
157 l_extended_deprn_flag,
158 l_period_name,
159 l_prorate_convention_code,
160 l_short_fiscal_year_flag,
161 l_transaction_name,
162 l_adjusted_rate,
163 l_allowed_deprn_limit,
164 l_allowed_deprn_limit_amount,
165 l_basic_rate,
166 l_cost,
167 l_deprn_reserve,
168 l_extended_depreciation_period,
169 l_fully_rsvd_revals_counter,
170 l_group_asset_id,
171 l_itc_amount_id,
172 l_life_in_months,
173 l_original_cost,
174 l_period_counter_fully_rsv,
175 l_production_capacity,
176 l_reval_amortization_basis,
177 l_reval_ceiling,
178 l_reval_reserve,
179 l_salvage_value,
180 l_unrevalued_cost,
181 l_ytd_deprn,
182 l_ytd_reval_deprn_expense,
183 l_amortization_start_date,
184 l_conversion_date,
185 l_date_placed_in_service,
186 l_original_deprn_start_date,
187 l_batch_id LIMIT l_batch_size;
188
189
190 if rc_extended_deprn%rowcount > 0 then
191
192 forall j in l_asset_number.first..l_asset_number.last
193 insert into fa_adjustments_t
194 (
195 asset_number,
196 asset_type,
197 attribute_category_code,
198 bonus_rule,
199 book_type_code,
200 ceiling_name,
201 depreciate_flag,
202 deprn_limit_type,
203 deprn_method_code,
204 description,
205 extended_deprn_flag,
206 period_name,
207 prorate_convention_code,
208 short_fiscal_year_flag,
209 transaction_name,
210 adjusted_rate,
211 allowed_deprn_limit,
212 allowed_deprn_limit_amount,
213 basic_rate,
214 cost,
215 deprn_reserve,
216 extended_depreciation_period,
217 fully_rsvd_revals_counter,
218 group_asset_id,
219 itc_amount_id,
220 life_in_months,
221 original_cost,
222 period_counter_fully_reserved,
223 production_capacity,
224 reval_amortization_basis,
225 reval_ceiling,
226 reval_reserve,
227 salvage_value,
228 unrevalued_cost,
229 ytd_deprn,
230 ytd_reval_deprn_expense,
231 amortization_start_date,
232 conversion_date,
233 date_placed_in_service,
234 original_deprn_start_date,
235 batch_id,
236 request_id
237 )
238 values
239 (
240 l_asset_number(j),
241 l_asset_type(j),
242 l_attribute_category_code(j),
243 l_bonus_rule(j),
244 l_book_type_code(j),
245 l_ceiling_name(j),
246 l_depreciate_flag(j),
247 l_deprn_limit_type(j),
248 l_deprn_method_code(j),
249 l_description(j),
250 l_extended_deprn_flag(j),
251 l_period_name(j),
252 l_prorate_convention_code(j),
253 l_short_fiscal_year_flag(j),
254 l_transaction_name(j),
255 l_adjusted_rate(j),
256 l_allowed_deprn_limit(j),
257 l_allowed_deprn_limit_amount(j),
258 l_basic_rate(j),
259 l_cost(j),
260 l_deprn_reserve(j),
261 l_extended_depreciation_period(j),
262 l_fully_rsvd_revals_counter(j),
263 l_group_asset_id(j),
264 l_itc_amount_id(j),
265 l_life_in_months(j),
266 l_original_cost(j),
267 l_period_counter_fully_rsv(j),
268 l_production_capacity(j),
269 l_reval_amortization_basis(j),
270 l_reval_ceiling(j),
271 l_reval_reserve(j),
272 l_salvage_value(j),
273 l_unrevalued_cost(j),
274 l_ytd_deprn(j),
275 l_ytd_reval_deprn_expense(j),
276 l_amortization_start_date(j),
277 l_conversion_date(j),
278 l_date_placed_in_service(j),
279 l_original_deprn_start_date(j),
280 l_batch_id(j),
281 -1*l_batch_id(j));
282
283 end if;
284
285 commit work;
286
287 exit when rc_extended_deprn%notfound;
288
289 end loop;
290
291 close rc_extended_deprn;
292
293 -- set the value of extended_deprn_period_name for assets with extended_deprn_flag = 'Y'
294 if (action_flag = 'U')then -- taking cases when Y->N is done or Y->Y ro Y->U
295
296 for i in c_books loop
297 l_count := l_count + 1;
298
299 select cp.period_name name
300 into l_name
301 from fa_book_controls bc
302 , fa_fiscal_year fy
303 , fa_calendar_types ct
304 , fa_calendar_periods cp
305 , fa_adjustments_t fat
306 where bc.book_type_code = i.book
307 and fat.batch_id = p_batch_id
308 and fat.asset_number = i.anum
309 and bc.deprn_calendar = ct.calendar_type
310 and cp.calendar_type = ct.calendar_type
311 and bc.fiscal_year_name = ct.fiscal_year_name
312 and fy.fiscal_year_name = ct.fiscal_year_name
313 and cp.period_num = 1
314 and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
315 and fy.fiscal_year = (fat.extended_depreciation_period-1)/ct.number_per_fiscal_year
316 and cp.start_date = fy.start_date;
317
318 update fa_adjustments_t
319 set extended_deprn_period_name = l_name
320 , extended_deprn_limit = i.limit_amt
321 where book_type_code = i.book
322 and asset_number = i.anum
323 and batch_id = p_batch_id;
324
325 if( mod(l_count,l_batch_size) = 0 )then
326 commit work;
327 end if;
328
329 end loop;
330 end if;
331
332 -- set the period name of period when fully reserved
333 l_count := 0;
334
335 for i in set_period_name loop
336 l_count := l_count + 1;
337
338 update fa_adjustments_t
339 set period_name = i.name
340 where book_type_code = i.book
341 and asset_number = i.num
342 and batch_id = p_batch_id;
343
344 if( mod(l_count,l_batch_size) = 0 )then
345 commit work;
346 end if;
347
348 end loop;
349
350 l_count := 0;
351 if (action_flag = 'Y')then
352 -- default the value of extended_deprn_period_name to the first period in next fiscal year
353 for i in all_books loop
354
355 select cp.period_name name
356 , fy.fiscal_year*ct.number_per_fiscal_year + 1 pctr
357 into l_name
358 , l_pctr
359 from fa_books bks
360 , fa_book_controls bc
361 , fa_fiscal_year fy
362 , fa_calendar_types ct
363 , fa_calendar_periods cp
364 where bc.book_type_code = i.book
365 and bks.asset_id = i.aid
366 and bc.book_type_code = bks.book_type_code
367 and bc.deprn_calendar = ct.calendar_type
368 and cp.calendar_type = ct.calendar_type
369 and bc.fiscal_year_name = ct.fiscal_year_name
370 and fy.fiscal_year_name = ct.fiscal_year_name
371 and cp.period_num = 1
372 and cp.start_date >= to_date('01/04/2007', 'DD/MM/RRRR')
373 and fy.fiscal_year = decode(sign(2007 -
374 decode(mod(bks.period_counter_fully_reserved,ct.number_per_fiscal_year)
375 , 0 , (bks.period_counter_fully_reserved-1)/ct.number_per_fiscal_year
376 , bks.period_counter_fully_reserved/ct.number_per_fiscal_year))
377 , 1, 2007
378 , ceil((bks.period_counter_fully_reserved)/ct.number_per_fiscal_year))
379 and cp.start_date = fy.start_date
380 and bks.period_counter_fully_reserved is not null
381 and bks.transaction_header_id_out is null;
382
383
384 update fa_adjustments_t
385 set extended_deprn_period_name = l_name
386 , extended_depreciation_period = l_pctr
387 , posting_status = 'POST'
388 , extended_deprn_flag = 'Y'
389 , extended_deprn_limit = 1
390 where book_type_code = i.book
391 and asset_number = i.anum
392 and batch_id = p_batch_id;
393
394 if( mod(l_count,l_batch_size) = 0 )then
395 commit work;
396 end if;
397
398 l_count := l_count + 1;
399
400 end loop;
401
405 , extended_deprn_flag = action_flag
402 elsif (action_flag <> 'U') then -- flag U indicates no action to be taken, as Avail Extended Deprn was Undecided for Bulk operation
403 update fa_adjustments_t
404 set posting_status = 'POST'
406 where batch_id = p_batch_id;
407
408 commit work;
409
410 end if;
411
412 exception when others then
413 v_err_code := sqlcode;
414 v_err_msg := sqlerrm(sqlcode);
415 if (g_print_debug) then
416 fa_debug_pkg.add(l_calling_fn, 'Bulk Collect or Insert','');
417 fa_debug_pkg.add(l_calling_fn, 'Error Code',v_err_code);
418 fa_debug_pkg.add(l_calling_fn, 'Error Message',v_err_msg);
419 end if;
420
421 end;
422
423 END FA_ADJUSTMENTS_T_PKG;