[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_REVAL_UTILITIES
Source
1 PACKAGE BODY IGI_IAC_REVAL_UTILITIES AS
2 -- $Header: igiiarub.pls 120.20.12000000.1 2007/08/01 16:18:16 npandya ship $
3
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER;
6 g_proc_level NUMBER;
7 g_event_level NUMBER;
8 g_excep_level NUMBER;
9 g_error_level NUMBER;
10 g_unexp_level NUMBER;
11 g_path VARCHAR2(100);
12 --===========================FND_LOG.END=====================================
13 l_rec igi_iac_revaluation_rates%rowtype;
14 -- create this for quicker access via sql navigator
15
16 -- FND log .. Stubbed the following procedures since they should not be used .. Begin
17
18 function debug return boolean is
19 begin
20 return false;
21 end;
22
23 function sqlplus_mode return boolean is
24 begin
25 return false;
26 end;
27
28 function logfile_mode return boolean
29 is begin
30 return true;
31 end;
32
33 function set_logfile_mode_on return boolean
34 is begin
35 return true;
36 end;
37
38 function set_logfile_mode_off return boolean is
39 begin
40 return true;
41 end;
42
43 procedure log ( p_calling_code in varchar2, p_mesg in varchar2 ) is
44 begin
45 null;
46 end;
47
48 -- FND log .. Stubbing of procedures .. End
49
50 function latest_adjustment ( fp_asset_id IN number
51 , fp_book_type_code in varchar2
52 )
53 return number is
54 l_transaction_id number;
55 l_mass_reference_id number;
56 l_adjustment_id number;
57 l_prev_adjustment_id number;
58 l_transaction_type_code varchar2(40);
59 l_adjustment_status varchar2(40);
60 l_path_name VARCHAR2(150);
61 begin
62 l_transaction_id := 0;
63 l_mass_reference_id := -1;
64 l_adjustment_id := -1;
65 l_prev_adjustment_id := -1;
66 l_path_name := g_path||'latest_adjustment';
67
68 if not IGI_IAC_COMMON_UTILS.Get_Latest_Transaction
69 ( X_book_type_code => fp_book_type_code
70 , X_asset_id => fp_asset_id
71 , X_Transaction_Type_Code => l_transaction_type_code
72 , X_Transaction_Id => l_transaction_id
73 , X_Mass_Reference_ID => l_mass_reference_id
74 , X_Adjustment_Id => l_adjustment_id
75 , X_Adjustment_Status => l_adjustment_status
76 , X_prev_adjustment_id => l_prev_adjustment_id
77 )
78 then
79 null;
80 end if;
81
82 return l_prev_adjustment_id;
83 exception when others then
84 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
85 return -1;
86 end;
87
88 function last_period_counter
89 ( p_asset_id number ,
90 p_book_type_code varchar2 ,
91 p_dpis_period_counter number ,
92 p_last_period_counter out NOCOPY number )
93 return boolean as
94 l_calendar_type varchar2(40) ;
95 l_number_per_fiscal_year number(4) ;
96 l_life_in_months number ;
97 l_path_name VARCHAR2(150) ;
98 begin
99 l_path_name := g_path||'last_period_counter';
100 select ct.calendar_type , ct.number_per_fiscal_year , bk.life_in_months
101 into l_calendar_type , l_number_per_fiscal_year , l_life_in_months
102 from fa_calendar_types ct , fa_book_controls bc , fa_books bk
103 where ct.calendar_type = bc.deprn_calendar
104 and bk.book_type_code = p_book_type_code
105 and bk.date_ineffective is null
106 and bk.asset_id = p_asset_id
107 and bc.date_ineffective is null
108 and bc.book_type_code = p_book_type_code ;
109
110 p_last_period_counter := p_dpis_period_counter + (( l_life_in_months/12 ) * l_number_per_fiscal_year ) - 1 ;
111
112 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
113 p_full_path => l_path_name,
114 p_string => '+last period counter '|| p_last_period_counter );
115
116 return true ;
117
118 exception
119 when others then
120 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
121 return false ;
122 end;
123
124 function Populate_Depreciation ( fp_asset_id IN number
125 , fp_book_type_code IN varchar2
126 , fp_period_counter IN number
127 , fp_hist_info IN OUT NOCOPY IGI_IAC_TYPES.fa_hist_asset_info
128 )
129 return boolean is
130
131 /* Bug 2763328 sekhar */
132 CURSOR c_get_deprn_calendar (p_book_type_code fa_books.book_type_code%type) IS
133 SELECT deprn_calendar
134 FROM fa_book_controls
135 WHERE book_type_code like p_book_type_code;
136
137 /* Bug 2763328 sekhar */
138 CURSOR c_get_periods_in_year(p_calendar_type fa_calendar_types.calendar_type%TYPE) IS
139 SELECT number_per_fiscal_year
140 FROM fa_calendar_types
141 WHERE calendar_type = p_calendar_type;
142
143 l_dpis_prd_rec IGI_IAC_TYPES.prd_rec;
144 l_curr_prd_rec IGI_IAC_TYPES.prd_rec;
145 l_num_of_periods_elapsed number;
146 l_num_of_periods_in_pyr number;
147 l_num_of_periods_in_cyr number;
148 l_last_period_counter number;
149 l_num_of_periods_total number;
150 fp_hist_info_old IGI_IAC_TYPES.fa_hist_asset_info;
151 l_deprn_calendar fa_calendar_types.calendar_type%TYPE;
152 l_periods_in_year fa_calendar_types.number_per_fiscal_year%TYPE;
153 l_path_name VARCHAR2(150);
154
155 begin
156 l_path_name := g_path||'populate_depreciation';
157 fp_hist_info_old := fp_hist_info;
158
159 if not IGI_IAC_COMMON_UTILS.get_period_info_for_date
160 ( p_book_type_code => fp_book_type_code
161 , p_date => fp_hist_info.date_placed_in_service
162 , p_prd_rec => l_dpis_prd_rec
163 )
164 then
165 return false;
166 end if;
167
168 if not IGI_IAC_COMMON_UTILS.get_period_info_for_counter
169 ( p_book_type_code => fp_book_type_code
170 , p_period_counter => fp_period_counter
171 , p_prd_rec => l_curr_prd_rec
172 )
173 then
174 return false;
175 end if;
176
177 /* check if the asset has been fully reserved before its life */
178 /* if asset is not yet fully reserved, then forecast the expiry period counter */
179
180 if fp_hist_info.period_counter_fully_reserved is null
181 then
182 if not last_period_counter ( p_asset_id => fp_asset_id,
183 p_book_type_code => fp_book_type_code ,
184 p_dpis_period_counter => l_dpis_prd_rec.period_counter ,
185 p_last_period_counter => l_last_period_counter
186 )
187 then
188 return false;
189 end if;
190 else
191 /* Bug 2763328 sekhar
192 Revalution creating wrong account enntries for fully reserevd assets
193 The fully resreved counter period in FA is not equal to actual fully reserved counter
194 this period counter will be the period counter on the period action performed rarther than actual period counter
195 modified code to get the actual fully reserved counter rarther than using the FA fully resereved period counter */
196
197 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
198 p_full_path => l_path_name,
199 p_string => ' Fully reserved asset');
200 OPEN c_get_deprn_calendar(fp_book_type_code);
201 FETCH c_get_deprn_calendar INTO l_deprn_calendar;
202 CLOSE c_get_deprn_calendar;
203
204 OPEN c_get_periods_in_year(l_deprn_calendar);
205 FETCH c_get_periods_in_year INTO l_periods_in_year;
206 CLOSE c_get_periods_in_year;
207
208 l_last_period_counter := (l_dpis_prd_rec.period_counter + ceil((fp_hist_info.life_in_months*l_periods_in_year)/12) - 1);
209 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
210 p_full_path => l_path_name,
211 p_string => ' Last Period counter :'||to_char(l_last_period_counter));
212 -- removed the following code
213 --l_last_period_counter := fp_hist_info.period_counter_fully_reserved ;
214 /* Bug 2763328 sekhar*/
215 end if;
216
217 /* set the local variables for ytd calculation */
218
219 l_num_of_periods_total := l_last_period_counter - l_dpis_prd_rec.period_counter + 1;
220 l_num_of_periods_elapsed := l_curr_prd_rec.period_counter - l_dpis_prd_rec.period_counter + 1;
221 l_num_of_periods_in_cyr := l_curr_prd_rec.period_num;
222
223 if l_curr_prd_rec.fiscal_year = l_dpis_prd_rec.fiscal_year then
224 l_num_of_periods_in_cyr := l_curr_prd_rec.period_num -
225 l_dpis_prd_rec.period_num + 1;
226 end if;
227 /* here we assume that if the asset is fully reserved */
228
229 if l_last_period_counter < l_curr_prd_rec.period_counter then
230 declare
231 l_prd_rec IGI_IAC_TYPES.prd_rec;
232 begin
233 if not IGI_IAC_COMMON_UTILS.get_period_info_for_counter
234 ( p_book_type_code => fp_book_type_code
235 , p_period_counter => l_last_period_counter
236 , p_prd_rec => l_prd_rec
237 )
238 then
239 return false;
240 end if;
241
242 if l_prd_rec.fiscal_year = l_curr_prd_rec.fiscal_year then
243 l_num_of_periods_in_cyr := l_prd_rec.period_num;
244 l_num_of_periods_elapsed := l_prd_rec.period_counter
245 - l_dpis_prd_rec.period_counter + 1;
246 elsif l_prd_rec.fiscal_year < l_curr_prd_rec.fiscal_year then
247 l_num_of_periods_in_cyr := 0;
248 l_num_of_periods_elapsed := l_prd_rec.period_counter
249 - l_dpis_prd_rec.period_counter + 1;
250 else
251 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
252 p_full_path => l_path_name,
253 p_string => 'fiscal year of the period fully reserved is incorrect');
254 end if;
255 exception
256 when others then
257 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
258 null;
259 end;
260 end if;
261
262 l_num_of_periods_in_pyr := l_num_of_periods_elapsed - l_num_of_periods_in_cyr;
263
264 fp_hist_info.deprn_periods_elapsed := l_num_of_periods_elapsed;
265 fp_hist_info.deprn_periods_current_year := l_num_of_periods_in_cyr;
266 fp_hist_info.deprn_periods_prior_year := l_num_of_periods_in_pyr;
267
268 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
269 p_full_path => l_path_name,
270 p_string => '+periods elapsed '|| l_num_of_periods_elapsed );
271 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
272 p_full_path => l_path_name,
273 p_string => '+periods cy '|| l_num_of_periods_in_cyr );
274 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
275 p_full_path => l_path_name,
276 p_string => '+periods py '|| l_num_of_periods_in_pyr );
277 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
278 p_full_path => l_path_name,
279 p_string => '+period ctr last '|| l_last_period_counter );
280
281 /* YTD deprn = num of periods in cy/num of periods elapsed * Acc Deprn */
282
283 fp_hist_info.ytd_deprn := ( fp_hist_info.deprn_periods_current_year
284 / fp_hist_info.deprn_periods_elapsed ) *
285 fp_hist_info.deprn_reserve;
286
287 /* PYS acc deprn = no of periods in py/number of periods elapsed * acc deprn */
288
289 fp_hist_info.pys_deprn_reserve := ( fp_hist_info.deprn_periods_prior_year
290 / fp_hist_info.deprn_periods_elapsed ) *
291 fp_hist_info.deprn_reserve;
292 --
293 -- this should work for assets after catchup
294 --
295 fp_hist_info.deprn_amount := fp_hist_info.deprn_reserve/fp_hist_info.deprn_periods_elapsed;
296
297 return true;
298 exception when others then
299 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
300 fp_hist_info := fp_hist_info_old;
301 return false;
302 end;
303
304
305 function split_rates ( fp_asset_id IN number
306 , fp_book_type_code IN varchar2
307 , fp_revaluation_id IN number
308 , fp_period_counter IN number
309 , fp_current_factor IN number
310 , fp_reval_type IN varchar2
311 , fp_first_time_flag IN boolean
312 , fp_mixed_scenario OUT NOCOPY BOOLEAN
313 , fp_reval_prev_rate_info IN IGI_IAC_TYPES.iac_reval_rate_params
314 , fp_reval_curr_rate_info_first OUT NOCOPY IGI_IAC_TYPES.iac_reval_rate_params
315 , fp_reval_curr_rate_info_next OUT NOCOPY IGI_IAC_TYPES.iac_reval_rate_params
316 )
317 return boolean is
318 l_reval_type igi_iac_reval_asset_rules.revaluation_type%type;
319 l_current_rate number;
320 l_cumulative_rate number;
321 fp_reval_curr_first_old IGI_IAC_TYPES.iac_reval_rate_params;
322 fp_reval_curr_next_old IGI_IAC_TYPES.iac_reval_rate_params;
323 l_path_name VARCHAR2(150);
324
325 begin
326 l_path_name := g_path||'split_rates';
327 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
328 p_full_path => l_path_name,
329 p_string => 'begin split_rates');
330 if fp_first_time_flag then /* only one movement possible */
331 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
332 p_full_path => l_path_name,
333 p_string => '+first time flag set');
334 end if;
335 fp_reval_curr_rate_info_first := fp_reval_prev_rate_info ;
336 fp_reval_curr_rate_info_next := fp_reval_prev_rate_info ;
337
338 select fp_current_factor, fp_Reval_type
339 into l_current_rate , l_reval_type
340 from sys.dual
341 ;
342 /* initialize the new rate record */
343
344 fp_reval_curr_rate_info_first.revaluation_id := fp_revaluation_id;
345 fp_reval_curr_rate_info_first.period_counter := fp_period_counter;
346 fp_reval_curr_rate_info_first.reval_type := l_reval_type;
347 fp_reval_curr_rate_info_next := fp_reval_curr_rate_info_first;
348
349 l_cumulative_rate := fp_reval_prev_rate_info.cumulative_reval_factor;
350 l_cumulative_rate := l_current_rate * l_cumulative_rate;
351
352 if l_cumulative_rate > 1 and fp_reval_prev_rate_info.cumulative_reval_factor < 1 then
353 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
354 p_full_path => l_path_name,
355 p_string => '+Upwards Mixed case scenario');
356 -- prev till cummulative rate =1 as first set.
357 -- cumm rate till current as second set.
358 fp_reval_curr_rate_info_first.current_reval_factor :=
359 (1/fp_reval_prev_rate_info.cumulative_reval_factor );
360 fp_reval_curr_rate_info_first.cumulative_reval_factor := 1;
361 fp_reval_curr_rate_info_next.current_reval_factor := l_cumulative_rate;
362 fp_reval_curr_rate_info_next.cumulative_reval_factor := l_cumulative_rate;
363 fp_reval_curr_rate_info_next.latest_record := 'Y';
364 fp_mixed_scenario := true;
365 elsif l_cumulative_rate < 1 and fp_reval_prev_rate_info.cumulative_reval_factor > 1 then
366 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
367 p_full_path => l_path_name,
368 p_string => '+Downwards Mixed case scenario');
369 fp_reval_curr_rate_info_first.current_reval_factor :=
370 (1/fp_reval_prev_rate_info.cumulative_reval_factor );
371 fp_reval_curr_rate_info_first.cumulative_reval_factor := 1;
372 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
373 p_full_path => l_path_name,
374 p_string => '+Second set rate '|| l_cumulative_rate );
375 fp_reval_curr_rate_info_next.current_reval_factor := l_cumulative_rate;
376 fp_reval_curr_rate_info_next.cumulative_reval_factor := l_cumulative_rate;
377 fp_reval_curr_rate_info_next.latest_record := 'Y';
378 fp_mixed_scenario := true;
379 elsif l_cumulative_rate = 1 and fp_reval_prev_rate_info.cumulative_reval_factor = 1 then
380 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
381 p_full_path => l_path_name,
382 p_string => '+Static revaluation');
383 fp_reval_curr_rate_info_first.current_reval_factor := 1;
384 fp_reval_curr_rate_info_first.cumulative_reval_factor := 1;
385 fp_reval_curr_rate_info_first.latest_record := 'Y';
386 fp_mixed_scenario := false;
387 elsif l_cumulative_rate > 1 and fp_reval_prev_rate_info.cumulative_reval_factor >= 1 then
388 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
389 p_full_path => l_path_name,
390 p_string => '+Upwards revaluation');
391 fp_reval_curr_rate_info_first.current_reval_factor := l_cumulative_rate/
392 fp_reval_prev_rate_info.cumulative_reval_factor ;
393 fp_reval_curr_rate_info_first.cumulative_reval_factor := l_cumulative_rate;
394 fp_reval_curr_rate_info_first.latest_record := 'Y';
395 fp_mixed_scenario := false;
396 elsif l_cumulative_rate = 1 and fp_reval_prev_rate_info.cumulative_reval_factor > 1 then
397 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
398 p_full_path => l_path_name,
399 p_string => '+Upwards revaluation');
400 fp_reval_curr_rate_info_first.current_reval_factor := l_cumulative_rate/
401 fp_reval_prev_rate_info.cumulative_reval_factor ;
402 fp_reval_curr_rate_info_first.cumulative_reval_factor := l_cumulative_rate;
403 fp_reval_curr_rate_info_first.latest_record := 'Y';
404 fp_mixed_scenario := false;
405 elsif l_cumulative_rate < 1 and fp_reval_prev_rate_info.cumulative_reval_factor <= 1 then
406 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
407 p_full_path => l_path_name,
408 p_string => '+Downwards revaluation');
409 fp_reval_curr_rate_info_first.current_reval_factor := l_cumulative_rate/
410 fp_reval_prev_rate_info.cumulative_reval_factor ;
411 fp_reval_curr_rate_info_first.cumulative_reval_factor := l_cumulative_rate;
412 fp_reval_curr_rate_info_first.latest_record := 'Y';
413 fp_mixed_scenario := false;
414 elsif l_cumulative_rate = 1 and fp_reval_prev_rate_info.cumulative_reval_factor < 1 then
415 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
416 p_full_path => l_path_name,
417 p_string => '+Downwards revaluation');
418 fp_reval_curr_rate_info_first.current_reval_factor := l_cumulative_rate/
419 fp_reval_prev_rate_info.cumulative_reval_factor ;
420 fp_reval_curr_rate_info_first.cumulative_reval_factor := l_cumulative_rate;
421 fp_reval_curr_rate_info_first.latest_record := 'Y';
422 fp_mixed_scenario := false;
423 end if;
424
425 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
426 p_full_path => l_path_name,
427 p_string => 'end split rates');
428 return true;
429 exception when others then
430 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
431 fp_reval_curr_rate_info_first := fp_reval_curr_first_old;
432 fp_reval_curr_rate_info_next := fp_reval_curr_next_old;
433 fp_mixed_scenario := FALSE;
434 return false;
435 end;
436
437 procedure display_prorate_dists ( fp_prorate_dists igi_iac_types.prorate_dists ) is
438 l_path_name VARCHAR2(150);
439 begin
440 l_path_name := g_path||'display_prorate_dists';
441 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
442 p_full_path => l_path_name,
443 p_string => '----------------------------------------------------');
444 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
445 p_full_path => l_path_name,
446 p_string => 'distribution id '|| fp_prorate_dists.distribution_id );
447 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
448 p_full_path => l_path_name,
449 p_string => 'ytd prorate factor '|| fp_prorate_dists.ytd_prorate_factor );
450 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
451 p_full_path => l_path_name,
452 p_string => 'normal prorate factor '|| fp_prorate_dists.normal_prorate_factor );
453 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
454 p_full_path => l_path_name,
455 p_string => 'latest period counter '|| fp_prorate_dists.latest_period_counter );
456 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
457 p_full_path => l_path_name,
458 p_string => 'units assigned '|| fp_prorate_dists.units_assigned );
459 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
460 p_full_path => l_path_name,
461 p_string => 'units active '|| fp_prorate_dists.units_active );
462 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
463 p_full_path => l_path_name,
464 p_string => '----------------------------------------------------');
465 end;
466
467 function prorate_dists ( fp_asset_id in number
468 , fp_book_type_code in varchar2
469 , fp_current_period_counter in number
470 , fp_prorate_dists_tab out NOCOPY igi_iac_types.prorate_dists_tab
471 , fp_prorate_dists_idx out NOCOPY binary_integer
472 )
473 return boolean is
474
475 l_prorate_dists_idx binary_integer ;
476 l_prorate_dists_tab igi_iac_types.prorate_dists_tab;
477 l_path_name VARCHAR2(150);
478
479 cursor c_deprn_asset is
480 select fds.asset_id, fds.book_type_code, fds.period_counter + 1, abs(fds.ytd_deprn) ytd_deprn, fdp.fiscal_year
481 , fadd.current_units total_units
482 from fa_deprn_summary fds, fa_deprn_periods fdp
483 , fa_additions fadd
484 where fds.book_type_code = fp_book_type_code
485 and fdp.book_type_code = fds.book_type_code
486 and fdp.period_counter = fds.period_counter
487 -- and fds.period_counter <= fp_current_period_counter
488 and fds.asset_id = fp_asset_id
489 and fadd.asset_id = fp_asset_id
490 and fds.period_counter in ( select max(period_counter)
491 from fa_deprn_summary
492 where book_type_code = fds.book_type_code
493 and asset_id = fds.asset_id
494 )
495 ;
496
497 cursor c_deprn_dists (cp_book_type_code in varchar2
498 , cp_asset_id in number
499 , cp_fiscal_year in number
500 , cp_ytd_deprn in number
501 , cp_total_units in number
502 ) is
503 /** we need this for reclass **/
504 select fdh.asset_id,
505 fdh.distribution_id,
506 fdp.period_counter latest_period_counter,
507 0 ytd_deprn,
508 fdp.period_num,
509 fdp.fiscal_year,
510 fdh.units_assigned,
511 nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
512 'ACTIVE' status,
513 (fdh.units_assigned/fadd.current_units) ytd_prorate_factor,
514 (fdh.units_assigned/fadd.current_units) normal_prorate_factor
515 from fa_deprn_periods fdp
516 , fa_distribution_history fdh
517 , fa_additions fadd
518 , fa_transaction_headers fth
519 where fth.book_type_code = cp_book_type_code
520 and fdp.book_type_code = fth.book_type_code
521 and fdh.asset_id = fth.asset_id
522 and fdh.transaction_header_id_out IS NULL
523 and fth.asset_id = cp_asset_id
524 and fadd.asset_id = fth.asset_id
525 and fth.transaction_type_code = 'RECLASS'
526 and fdp.period_counter in ( select distinct period_counter_created
527 from fa_adjustments
528 where book_type_code = fth.book_type_code
529 and asseT_id = fth.asset_id
530 and distribution_id = fdh.distribution_id
531 and transaction_header_id = fth.transaction_header_id
532 )
533 and not exists ( select distribution_id
534 from fa_deprn_detail
535 where asset_id = fth.asset_id
536 and book_type_code = fth.book_type_code
537 and distribution_id = fdh.distribution_id
538 )
539 union /** we need this for catchup **/
540 select fdd.asset_id,
541 fdd.distribution_id,
542 fdd.period_counter latest_period_counter,
543 fdd.ytd_deprn,
544 fdp.period_num,
545 fdp.fiscal_year,
546 fdh.units_assigned,
547 nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
548 'ACTIVE' status,
549 (fdh.units_assigned/cp_total_units) ytd_prorate_factor,
550 (fdh.units_assigned/cp_total_units) normal_prorate_factor
551 from fa_deprn_detail fdd
552 , fa_deprn_periods fdp
553 , fa_distribution_history fdh
554 where fdd.book_type_code = cp_book_type_code
555 and fdp.book_type_code = fdd.book_type_code
556 and fdp.period_counter = fdd.period_counter
557 and fdp.fiscal_year = cp_fiscal_year
558 and fdh.book_type_code = fdd.book_type_code
559 and fdh.asset_id = fdd.asset_id
560 and fdd.asset_id = cp_asset_id
561 and fdh.distribution_id = fdd.distribution_id
562 and fdh.transaction_header_id_out is null
563 and cp_total_units <> 0 -- avoid divide by zero issues
564 and ( fdd.asset_id, fdd.distribution_id, fdd.period_counter )
565 in ( select asset_id, distribution_id, max(period_counter)
566 from fa_deprn_detail
567 where book_type_code = fdd.book_type_code
568 and asset_id = fdd.asset_id
569 group by asset_id, distribution_id
570 );
571 begin
572 l_prorate_dists_idx := 0;
573 l_path_name := g_path||'prorate_dists';
574
575 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
576 p_full_path => l_path_name,
577 p_string => 'Begin prorate dists');
578 for l_asset in c_deprn_asset loop
579 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
580 p_full_path => l_path_name,
581 p_string => '+initialize variables');
582 l_prorate_dists_idx := 0;
583 l_prorate_dists_tab.delete;
584 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
585 p_full_path => l_path_name,
586 p_string => '+Paramteres variables ytd '|| l_asset.ytd_deprn );
587 for l_dists in c_deprn_dists ( cp_asset_id => l_asset.asset_id
588 , cp_book_type_code => l_asset.book_type_code
589 , cp_fiscal_year => l_asset.fiscal_year
590 , cp_ytd_deprn => l_asset.ytd_deprn
591 , cp_total_units => l_asset.total_units
592 )
593
594
595 loop
596 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
597 p_full_path => l_path_name,
598 p_string => '+Process variables');
599
600 l_prorate_dists_idx := l_prorate_dists_idx + 1;
601 l_prorate_dists_tab ( l_prorate_dists_idx ).distribution_id
602 := l_dists.distribution_id;
603 l_prorate_dists_tab ( l_prorate_dists_idx ).ytd_prorate_factor
604 := l_dists.ytd_prorate_factor;
605 l_prorate_dists_tab ( l_prorate_dists_idx ).normal_prorate_factor
606 := l_dists.normal_prorate_factor;
607 l_prorate_dists_tab ( l_prorate_dists_idx ).units_assigned
608 := l_dists.units_assigned;
609 l_prorate_dists_tab ( l_prorate_dists_idx ).units_active
610 := l_dists.units_active;
611 l_prorate_dists_tab ( l_prorate_dists_idx ).latest_period_counter
612 := l_dists.latest_period_counter;
613 if l_dists.normal_prorate_factor = 0 then
614 l_prorate_dists_tab ( l_prorate_dists_idx ).active_flag := 'N';
615 else
616 l_prorate_dists_tab ( l_prorate_dists_idx ).active_flag := null;
617 end if;
618 display_prorate_dists ( l_prorate_dists_tab ( l_prorate_dists_idx ) );
619 end loop;
620 end loop;
621 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
622 p_full_path => l_path_name,
623 p_string => '+final processing');
624 fp_prorate_dists_tab := l_prorate_dists_tab;
625 fp_prorate_dists_idx := l_prorate_dists_idx;
626 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
627 p_full_path => l_path_name,
628 p_string => 'end prorate dists');
629 return true;
630 exception when others then
631 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
632 return false;
633 end;
634
635 FUNCTION prorate_active_dists_YTD ( fp_asset_id in number
636 , fp_book_type_code in varchar2
637 , fp_current_period_counter in number
638 , fp_prorate_dists_tab out NOCOPY igi_iac_types.prorate_dists_tab
639 , fp_prorate_dists_idx out NOCOPY binary_integer
640 ) RETURN BOOLEAN IS
641 CURSOR c_active_dists IS
642 SELECT fdh.distribution_id distribution_id,
643 fdh.units_assigned units_assigned,
644 nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
645 fdp.period_counter period_counter_created,
646 fdp.fiscal_year fiscal_year
647 FROM fa_distribution_history fdh,
648 fa_deprn_periods fdp
649 WHERE fdh.book_type_code = fp_book_type_code
650 AND fdh.asset_id = fp_asset_id
651 AND fdh.transaction_header_id_out IS NULL
652 AND fdp.book_type_code = fp_book_type_code
653 AND fdh.date_effective BETWEEN fdp.period_open_date AND nvl(fdp.period_close_date,sysdate);
654
655 CURSOR c_get_dpis IS
656 SELECT fb.date_placed_in_service,
657 fb.period_counter_fully_reserved,
658 fb.life_in_months,
659 fb.depreciate_flag
660 FROM fa_books fb
661 WHERE fb.book_type_code = fp_book_type_code
662 AND fb.asset_id = fp_asset_id
663 AND fb.transaction_header_id_out IS NULL;
664
665 CURSOR C_first_deprn_period IS
666 SELECT min(fds.period_counter)
667 FROM fa_deprn_summary fds
668 WHERE fds.book_type_code = fp_book_type_code
669 AND fds.asset_id = fp_asset_id
670 AND fds.deprn_source_code = 'DEPRN';
671
672 CURSOR c_get_periods_in_year IS
673 SELECT ct.number_per_fiscal_year
674 FROM fa_calendar_types ct, fa_book_controls bc
675 WHERE ct.calendar_type = bc.deprn_calendar
676 AND bc.book_type_code = fp_book_type_code;
677
678 l_prorate_dists_idx binary_integer ;
679 l_prorate_dists_tab igi_iac_types.prorate_dists_tab;
680 l_path_name VARCHAR2(150);
681 l_fully_reserved NUMBER;
682 l_dpis DATE;
683 l_dpis_period igi_iac_types.prd_rec;
684 l_current_period igi_iac_types.prd_rec;
685 l_first_deprn_period NUMBER;
686 l_dist_first_period NUMBER;
687 l_dist_last_period NUMBER;
688 l_dist_active_periods NUMBER;
689 l_fully_reserved_counter NUMBER;
690 l_fully_reserved_period igi_iac_types.prd_rec;
691 l_periods_per_FY fa_calendar_types.number_per_fiscal_year%TYPE;
692 l_total_periods NUMBER;
693 l_last_period igi_iac_types.prd_rec;
694 l_asset_prorate_units NUMBER;
695 l_life_in_months NUMBER;
696 l_depreciate_flag VARCHAR2(3);
697
698 BEGIN
699
700 l_path_name := g_path||'prorate_active_dists_YTD';
701 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
702 p_string => 'Start of Processing');
703
704 l_prorate_dists_idx := 0;
705 l_prorate_dists_tab.delete;
706 l_asset_prorate_units := 0;
707
708 OPEN c_get_dpis;
709 FETCH c_get_dpis INTO l_dpis, l_fully_reserved, l_life_in_months, l_depreciate_flag;
710 CLOSE c_get_dpis;
711
712 IF NOT igi_iac_common_utils.Get_Period_Info_for_Date( fp_book_type_code ,
713 l_dpis ,
714 l_dpis_period) THEN
715
716 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
717 p_string => 'Error in fetching DPIS period information ');
718 RETURN FALSE;
719 END IF;
720 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
721 p_string => 'DPIS Period:'||l_dpis_period.period_counter);
722
723 IF NOT igi_iac_common_utils.Get_Period_Info_for_Counter( fp_book_type_code ,
724 fp_current_period_counter ,
725 l_current_period) THEN
726 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
727 p_string => 'Error in fetching current period information ');
728 RETURN FALSE;
729 END IF;
730 l_last_period := l_current_period;
731 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
732 p_string => 'Current Period:'||l_current_period.period_counter);
733
734 IF l_fully_reserved IS NOT NULL THEN
735 OPEN c_get_periods_in_year;
736 FETCH c_get_periods_in_year INTO l_periods_per_FY;
737 CLOSE c_get_periods_in_year;
738
739 l_total_periods := ceil((l_life_in_months*l_periods_per_FY)/12);
740 l_fully_reserved_counter := (l_dpis_period.period_counter + l_total_periods - 1);
741
742 IF NOT igi_iac_common_utils.Get_Period_Info_for_Counter( fp_book_type_code ,
743 l_fully_reserved_counter ,
744 l_fully_reserved_period) THEN
745 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
746 p_string => 'Error in fetching fully reserved period information ');
747
748 RETURN FALSE;
749 END IF;
750 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
751 p_string => 'Fully Reserved Period:'||l_fully_reserved_counter);
752
753 l_last_period := l_fully_reserved_period;
754 END IF;
755
756 OPEN c_first_deprn_period;
757 FETCH c_first_deprn_period INTO l_first_deprn_period;
758 CLOSE c_first_deprn_period;
759 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
760 p_string => 'First Deprn Period:'||l_first_deprn_period);
761
762 FOR l_dist IN C_active_dists LOOP
763 l_prorate_dists_idx := l_prorate_dists_idx + 1;
764
765 l_prorate_dists_tab ( l_prorate_dists_idx ).distribution_id
766 := l_dist.distribution_id;
767 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
768 p_string => 'Distribution Id:'||l_dist.distribution_id);
769
770 IF (l_last_period.fiscal_year < l_current_period.fiscal_year) OR (l_depreciate_flag='NO') THEN
771 l_dist_active_periods := 0;
772 ELSE
773 IF l_dist.period_counter_created = nvl(l_first_deprn_period,l_dist.period_counter_created) THEN
774 IF l_dpis_period.fiscal_year = l_last_period.fiscal_year THEN
775 l_dist_active_periods :=
776 (l_last_period.period_counter - l_dpis_period.period_counter + 1);
777 ELSE
778 l_dist_active_periods := l_last_period.period_num;
779 END IF;
780 ELSE
781 IF l_dist.fiscal_year = l_last_period.fiscal_year THEN
782 IF l_last_period.period_counter >= l_dist.period_counter_created THEN
783 l_dist_active_periods :=
784 l_last_period.period_counter - l_dist.period_counter_created + 1;
785 ELSE
786 l_dist_active_periods := 0;
787 END IF;
788 ELSE
789 l_dist_active_periods := l_last_period.period_num;
790 END IF;
791 END IF;
792 END IF;
793 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
794 p_string => 'Active Periods:'||l_dist_active_periods);
795 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
796 p_string => 'Units_assigned:'||l_dist.units_assigned);
797
798 l_prorate_dists_tab ( l_prorate_dists_idx ).ytd_prorate_factor
799 := l_dist_active_periods * l_dist.units_assigned;
800 l_asset_prorate_units := l_asset_prorate_units +
801 l_prorate_dists_tab ( l_prorate_dists_idx ).ytd_prorate_factor;
802 END LOOP;
803 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
804 p_string => 'Asset total active units:'||l_asset_prorate_units);
805
806 FOR i IN 1..l_prorate_dists_idx LOOP
807 IF l_asset_prorate_units = 0 THEN
808 l_prorate_dists_tab ( i ).ytd_prorate_factor := 0;
809 ELSE
810 l_prorate_dists_tab ( i ).ytd_prorate_factor
811 := l_prorate_dists_tab(i).ytd_prorate_factor/l_asset_prorate_units;
812 END IF;
813 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
814 p_string => 'Distribution Id:'||l_prorate_dists_tab(i).distribution_id);
815 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
816 p_string => 'Prorate Factor:'||l_prorate_dists_tab(i).ytd_prorate_factor);
817
818 END LOOP;
819 fp_prorate_dists_idx := l_prorate_dists_idx;
820 fp_prorate_dists_tab := l_prorate_dists_tab;
821 RETURN TRUE;
822 EXCEPTION
823 WHEN OTHERS THEN
824 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
825 RETURN FALSE;
826 END prorate_active_dists_YTD;
827
828 FUNCTION prorate_all_dists_YTD ( fp_asset_id in number
829 , fp_book_type_code in varchar2
830 , fp_current_period_counter in number
831 , fp_prorate_dists_tab out NOCOPY igi_iac_types.prorate_dists_tab
832 , fp_prorate_dists_idx out NOCOPY binary_integer
833 ) RETURN BOOLEAN IS
834
835 CURSOR c_all_dists (cp_fiscal_year number) IS
836 SELECT fdh.distribution_id distribution_id,
837 fdh.units_assigned units_assigned,
838 nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
839 fdp.period_counter period_counter_created,
840 Null period_counter_closed ,
841 fdp.fiscal_year fiscal_year,'Y' Active_flag
842 FROM fa_distribution_history fdh,
843 fa_deprn_periods fdp
844 WHERE fdh.book_type_code = fp_book_type_code
845 AND fdh.asset_id = fp_asset_id
846 AND fdh.transaction_header_id_out IS NULL
847 AND fdp.book_type_code = fp_book_type_code
848 AND fdh.date_effective BETWEEN fdp.period_open_date AND nvl(fdp.period_close_date,sysdate)
849 UNION ALL
850 SELECT fdh.distribution_id distribution_id,
851 fdh.units_assigned units_assigned,
852 nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
853 fdp2.period_counter period_counter_created,
854 fdp3.period_counter period_counter_closed,
855 fdp2.fiscal_year fiscal_year,'N' Active_flag
856 FROM fa_distribution_history fdh,
857 fa_deprn_periods fdp1,
858 fa_deprn_periods fdp2,
859 fa_deprn_periods fdp3
860 WHERE fdh.book_type_code = fp_book_type_code
861 AND fdh.asset_id = fp_asset_id
862 AND fdh.transaction_header_id_out IS Not NULL
863 AND fdp1.book_type_code = fdh.book_type_code
864 AND fdp1.period_counter = (select min(period_counter)
865 from fa_deprn_periods fdep
866 where book_type_code = fp_book_type_code
867 and fiscal_year=cp_fiscal_year)
868 AND fdp2.book_type_code = fdh.book_type_code
869 AND fdh.date_effective between fdp2.period_open_date and nvl(fdp2.period_close_date,fdh.date_effective)
870 AND fdp3.book_type_code = fdh.book_type_code
871 AND fdh.date_ineffective between fdp3.period_open_date and nvl(fdp3.period_close_date,fdh.date_ineffective);
872
873 CURSOR c_get_dpis IS
874 SELECT fb.date_placed_in_service,
875 fb.period_counter_fully_reserved,
876 fb.life_in_months,
877 fb.depreciate_flag
878 FROM fa_books fb
879 WHERE fb.book_type_code = fp_book_type_code
880 AND fb.asset_id = fp_asset_id
881 AND fb.transaction_header_id_out IS NULL;
882
883 CURSOR C_first_deprn_period IS
884 SELECT min(fds.period_counter)
885 FROM fa_deprn_summary fds
886 WHERE fds.book_type_code = fp_book_type_code
887 AND fds.asset_id = fp_asset_id
888 AND fds.deprn_source_code = 'DEPRN';
889
890 CURSOR c_get_periods_in_year IS
891 SELECT ct.number_per_fiscal_year
892 FROM fa_calendar_types ct, fa_book_controls bc
893 WHERE ct.calendar_type = bc.deprn_calendar
894 AND bc.book_type_code = fp_book_type_code;
895
896 l_prorate_dists_idx binary_integer ;
897 l_prorate_dists_tab igi_iac_types.prorate_dists_tab;
898 l_path_name VARCHAR2(150);
899 l_fully_reserved NUMBER;
900 l_dpis DATE;
901 l_dpis_period igi_iac_types.prd_rec;
902 l_current_period igi_iac_types.prd_rec;
903 l_first_deprn_period NUMBER;
904 l_dist_first_period NUMBER;
905 l_dist_last_period NUMBER;
906 l_dist_active_periods NUMBER;
907 l_fully_reserved_counter NUMBER;
908 l_fully_reserved_period igi_iac_types.prd_rec;
909 l_periods_per_FY fa_calendar_types.number_per_fiscal_year%TYPE;
910 l_total_periods NUMBER;
911 l_last_period igi_iac_types.prd_rec;
912 l_asset_prorate_units NUMBER;
913 l_life_in_months NUMBER;
914 l_depreciate_flag VARCHAR2(3);
915 l_last_dist_period igi_iac_types.prd_rec;
916 l_first_dist_period igi_iac_types.prd_rec;
917
918 BEGIN
919
920 l_path_name := g_path||'prorate_all_dists_YTD';
921 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
922 p_string => 'Start of Processing');
923
924 l_prorate_dists_idx := 0;
925 l_prorate_dists_tab.delete;
926 l_asset_prorate_units := 0;
927
928 OPEN c_get_dpis;
929 FETCH c_get_dpis INTO l_dpis, l_fully_reserved, l_life_in_months, l_depreciate_flag;
930 CLOSE c_get_dpis;
931
932 IF NOT igi_iac_common_utils.Get_Period_Info_for_Date( fp_book_type_code ,
933 l_dpis ,
934 l_dpis_period) THEN
935 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
936 p_string => 'Error in fetching DPIS period information ');
937 RETURN FALSE;
938 END IF;
939 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
940 p_string => 'DPIS Period:'||l_dpis_period.period_counter);
941
942 IF NOT igi_iac_common_utils.Get_Period_Info_for_Counter( fp_book_type_code ,
943 fp_current_period_counter ,
944 l_current_period) THEN
945 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
946 p_string => 'Error in fetching current period information ');
947 RETURN FALSE;
948 END IF;
949 l_last_period := l_current_period;
950 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
951 p_string => 'Current Period:'||l_current_period.period_counter);
952
953 IF l_fully_reserved IS NOT NULL THEN
954 OPEN c_get_periods_in_year;
955 FETCH c_get_periods_in_year INTO l_periods_per_FY;
956 CLOSE c_get_periods_in_year;
957
958 l_total_periods := ceil((l_life_in_months*l_periods_per_FY)/12);
959 l_fully_reserved_counter := (l_dpis_period.period_counter + l_total_periods - 1);
960
961 IF l_fully_reserved_counter > l_fully_reserved THEN
962 l_fully_reserved_counter := l_fully_reserved;
963 END IF;
964
965 IF NOT igi_iac_common_utils.Get_Period_Info_for_Counter( fp_book_type_code ,
966 l_fully_reserved_counter ,
967 l_fully_reserved_period) THEN
968 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
969 p_string => 'Error in fetching fully reserved period information ');
970
971 RETURN FALSE;
972 END IF;
973 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
974 p_string => 'Fully Reserved Period:'||l_fully_reserved_counter);
975
976 l_last_period := l_fully_reserved_period;
977 END IF;
978
979 OPEN c_first_deprn_period;
980 FETCH c_first_deprn_period INTO l_first_deprn_period;
981 CLOSE c_first_deprn_period;
982 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
983 p_string => 'First Deprn Period:'||l_first_deprn_period);
984
985 FOR l_dist IN C_all_dists(l_current_period.fiscal_year) LOOP
986
987 l_last_dist_period:=l_last_period;
988 l_first_dist_period:=l_dpis_period;
989 l_prorate_dists_idx := l_prorate_dists_idx + 1;
990
991 l_prorate_dists_tab ( l_prorate_dists_idx ).distribution_id
992 := l_dist.distribution_id;
993 l_prorate_dists_tab(l_prorate_dists_idx).active_flag := l_dist.active_flag;
994 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
995 p_string => 'Distribution Id:'||l_dist.distribution_id);
996 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
997 p_string => 'Active Flag:'||l_dist.active_flag);
998
999 IF l_dist.active_flag = 'N' THEN -- inactive
1000 IF l_last_dist_period.period_counter >= l_dist.period_counter_closed THEN --
1001 -- get info for the period counter closed.
1002 IF NOT igi_iac_common_utils.Get_Period_Info_for_Counter( fp_book_type_code ,
1003 l_dist.period_counter_closed - 1 ,
1004 l_last_dist_period) THEN
1005 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,p_full_path => l_path_name,
1006 p_string => 'Error in fetching dist closed period information ');
1007 RETURN FALSE;
1008 END IF;
1009 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1010 p_string => ' Distribution closed Period:'||l_last_dist_period.period_counter);
1011 END IF;--
1012 END IF;-- inactive end
1013
1014 IF (l_last_dist_period.fiscal_year < l_current_period.fiscal_year) OR (l_depreciate_flag='NO') THEN
1015 l_dist_active_periods := 0;
1016 ELSE
1017 IF l_dist.period_counter_created = nvl(l_first_deprn_period,l_dist.period_counter_created) THEN
1018 IF (l_dist.active_flag = 'N' AND l_dist.period_counter_created = l_dist.period_counter_closed) THEN
1019 l_dist_active_periods := 0;
1020 ELSE
1021 IF l_dpis_period.fiscal_year = l_last_dist_period.fiscal_year THEN
1022 l_dist_active_periods :=
1023 (l_last_dist_period.period_counter - l_dpis_period.period_counter + 1);
1024 ELSE
1025 l_dist_active_periods := l_last_dist_period.period_num;
1026 END IF;
1027 END IF;
1028 ELSE
1029 IF l_dist.fiscal_year = l_last_dist_period.fiscal_year THEN
1030 IF l_last_dist_period.period_counter >= l_dist.period_counter_created THEN
1031 l_dist_active_periods :=
1032 l_last_dist_period.period_counter - l_dist.period_counter_created + 1;
1033 ELSE
1034 l_dist_active_periods := 0;
1035 END IF;
1036 ELSE
1037 l_dist_active_periods := l_last_dist_period.period_num;
1038 END IF;
1039 END IF;
1040 END IF;
1041 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1042 p_string => 'Active Periods:'||l_dist_active_periods);
1043 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1044 p_string => 'Units_assigned:'||l_dist.units_assigned);
1045
1046 l_prorate_dists_tab ( l_prorate_dists_idx ).ytd_prorate_factor
1047 := l_dist_active_periods * l_dist.units_assigned;
1048 l_asset_prorate_units := l_asset_prorate_units +
1049 l_prorate_dists_tab ( l_prorate_dists_idx ).ytd_prorate_factor;
1050 END LOOP;
1051 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1052 p_string => 'Asset total active units:'||l_asset_prorate_units);
1053
1054 FOR i IN 1..l_prorate_dists_idx LOOP
1055 IF l_asset_prorate_units = 0 THEN
1056 l_prorate_dists_tab ( i ).ytd_prorate_factor := 0;
1057 ELSE
1058 l_prorate_dists_tab ( i ).ytd_prorate_factor
1059 := l_prorate_dists_tab(i).ytd_prorate_factor/l_asset_prorate_units;
1060 END IF;
1061 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1062 p_string => 'Distribution Id:'||l_prorate_dists_tab(i).distribution_id);
1063 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,p_full_path => l_path_name,
1064 p_string => 'Prorate Factor:'||l_prorate_dists_tab(i).ytd_prorate_factor);
1065
1066 END LOOP;
1067 fp_prorate_dists_idx := l_prorate_dists_idx;
1068 fp_prorate_dists_tab := l_prorate_dists_tab;
1069 RETURN TRUE;
1070 EXCEPTION
1071 WHEN OTHERS THEN
1072 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1073 RETURN FALSE;
1074 END prorate_all_dists_YTD;
1075
1076 -- Bug 3434121
1077 -- The following procedure is a replica of the procedure gl_formsinfo.get_coa_info
1078 -- and is used to get the ordering segments for the category flexfield
1079
1080 PROCEDURE get_coa_info (x_chart_of_accounts_id IN NUMBER,
1081 x_segment_delimiter IN OUT NOCOPY VARCHAR2,
1082 x_enabled_segment_count IN OUT NOCOPY NUMBER,
1083 x_segment_order_by IN OUT NOCOPY VARCHAR2,
1084 x_accseg_segment_num IN OUT NOCOPY NUMBER,
1085 x_accseg_app_col_name IN OUT NOCOPY VARCHAR2,
1086 x_accseg_left_prompt IN OUT NOCOPY VARCHAR2,
1087 x_balseg_segment_num IN OUT NOCOPY NUMBER,
1088 x_balseg_app_col_name IN OUT NOCOPY VARCHAR2,
1089 x_balseg_left_prompt IN OUT NOCOPY VARCHAR2,
1090 x_ieaseg_segment_num IN OUT NOCOPY NUMBER,
1091 x_ieaseg_app_col_name IN OUT NOCOPY VARCHAR2,
1092 x_ieaseg_left_prompt IN OUT NOCOPY VARCHAR2) IS
1093
1094 CURSOR seg_count IS
1095 SELECT segment_num, application_column_name
1096 FROM fnd_id_flex_segments
1097 WHERE application_id = 140
1098 AND id_flex_code = 'CAT#'
1099 AND enabled_flag = 'Y'
1100 AND id_flex_num = x_chart_of_accounts_id
1101 ORDER BY segment_num;
1102
1103 dumdum BOOLEAN;
1104 x_seg_name VARCHAR2(30);
1105 x_value_set VARCHAR2(60);
1106 l_path_name VARCHAR2(150);
1107 BEGIN
1108 dumdum := FALSE;
1109 l_path_name := g_path||'get_coa_info';
1110 -- Identify the natural account and balancing segments
1111 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
1112 140, 'CAT#', x_chart_of_accounts_id,
1113 'GL_ACCOUNT', x_accseg_segment_num);
1114 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
1115 140, 'CAT#', x_chart_of_accounts_id,
1116 'GL_BALANCING', x_balseg_segment_num);
1117 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
1118 140, 'CAT#', x_chart_of_accounts_id,
1119 'GL_INTERCOMPANY', x_ieaseg_segment_num);
1120
1121 -- Get the segment delimiter
1122 x_segment_delimiter := FND_FLEX_APIS.get_segment_delimiter(
1123 140, 'CAT#', x_chart_of_accounts_id);
1124
1125 -- Count 'em up and string 'em together
1126 x_enabled_segment_count := 0;
1127 FOR r IN seg_count LOOP
1128 -- How many enabled segs are there?
1129 x_enabled_segment_count := seg_count%ROWCOUNT;
1130 -- Record the order by string
1131 IF seg_count%ROWCOUNT = 1 THEN
1132 x_segment_order_by := r.application_column_name;
1133 ELSE
1134 x_segment_order_by := x_segment_order_by||
1135 ','||
1136 r.application_column_name;
1137 END IF;
1138 -- If this is either the accseg or balseg, get more info
1139 IF r.segment_num = x_accseg_segment_num THEN
1140 IF (FND_FLEX_APIS.get_segment_info(
1141 140, 'CAT#', x_chart_of_accounts_id,
1142 r.segment_num, x_accseg_app_col_name,
1143 x_seg_name, x_accseg_left_prompt, x_value_set)) THEN
1144 null;
1145 END IF;
1146 ELSIF r.segment_num = x_balseg_segment_num THEN
1147 IF (FND_FLEX_APIS.get_segment_info(
1148 140, 'GL#', x_chart_of_accounts_id,
1149 r.segment_num, x_balseg_app_col_name,
1150 x_seg_name, x_balseg_left_prompt, x_value_set)) THEN
1151 null;
1152 END IF;
1153 ELSIF r.segment_num = x_ieaseg_segment_num THEN
1154 IF (FND_FLEX_APIS.get_segment_info(
1155 140, 'CAT#', x_chart_of_accounts_id,
1156 r.segment_num, x_ieaseg_app_col_name,
1157 x_seg_name, x_ieaseg_left_prompt, x_value_set)) THEN
1158 null;
1159 END IF;
1160 END IF;
1161 END LOOP;
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1166 app_exception.raise_exception;
1167 END get_coa_info;
1168
1169 FUNCTION Synchronize_Accounts(
1170 p_book_type_code IN VARCHAR2,
1171 p_period_counter IN NUMBER,
1172 p_calling_function IN VARCHAR2
1173 ) return BOOLEAN IS
1174
1175 CURSOR c_get_adjustments(c_adjustment_id number) IS
1176 SELECT rowid,
1177 adjustment_id,
1178 book_type_code,
1179 code_combination_id,
1180 adjustment_type,
1181 asset_id,
1182 distribution_id,
1183 period_counter
1184 FROM igi_iac_adjustments
1185 WHERE book_type_code = p_book_type_code
1186 AND period_counter = p_period_counter
1187 AND adjustment_type IN ('COST','RESERVE','EXPENSE')
1188 AND adjustment_id = c_adjustment_id for update;
1189
1190 CURSOR c_get_transaction IS
1191 SELECT adjustment_id,
1192 transaction_header_id,
1193 transaction_type_code
1194 FROM igi_iac_transaction_headers
1195 WHERE book_type_code = p_book_type_code
1196 AND period_counter = p_period_counter
1197 AND transaction_type_code in ('REVALUATION');
1198
1199 CURSOR c_get_accounts (c_distribution_id NUMBER) IS
1200 SELECT nvl(ASSET_COST_ACCOUNT_CCID, -1),
1201 nvl(DEPRN_EXPENSE_ACCOUNT_CCID, -1),
1202 nvl(DEPRN_RESERVE_ACCOUNT_CCID, -1),
1203 bc.accounting_flex_structure
1204 FROM FA_DISTRIBUTION_ACCOUNTS da,
1205 FA_BOOK_CONTROLS bc
1206 WHERE bc.book_type_code = p_book_type_code
1207 AND da.book_type_code = bc.book_type_code
1208 AND da.distribution_id = c_distribution_id;
1209
1210 CURSOR c_get_account_ccid ( c_asset_id NUMBER,
1211 c_distribution_id NUMBER,
1212 c_adjustment_source_type_code VARCHAR2,
1213 c_adjustment_type VARCHAR2,
1214 c_transaction_header_id NUMBER) IS
1215 SELECT code_combination_id
1216 FROM fa_adjustments
1217 WHERE book_type_code = p_book_type_code
1218 AND asset_id = c_asset_id
1219 AND distribution_id = c_distribution_id
1220 AND adjustment_type = c_adjustment_type;
1221
1222 l_account_ccid NUMBER;
1223 l_adjustment_type VARCHAR2(50);
1224 l_rowid rowid;
1225 l_cost_ccid NUMBER;
1226 l_expense_ccid NUMBER;
1227 l_reserve_ccid NUMBER;
1228 l_flex_num NUMBER;
1229 l_category_id NUMBER;
1230 l_default_ccid NUMBER;
1231 l_account_seg_val VARCHAR2(25);
1232 l_account_type VARCHAR2(100);
1233 l_acct_ccid NUMBER;
1234 l_asset_cost_acct VARCHAR2(25);
1235 l_dep_exp_acct VARCHAR2(25);
1236 l_dep_res_acct VARCHAR2(25);
1237 l_asset_cost_account_ccid NUMBER;
1238 l_reserve_account_ccid NUMBER;
1239 l_validation_date date;
1240 l_result BOOLEAN;
1241
1242 l_path VARCHAR2(100);
1243
1244 -- bulk fecthes
1245 TYPE rowed_type_tbl_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1246 TYPE adj_id_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS. ADJUSTMENT_ID%TYPE
1247 INDEX BY BINARY_INTEGER;
1248 TYPE book_type_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS.BOOK_TYPE_CODE%TYPE
1249 INDEX BY BINARY_INTEGER;
1250 TYPE code_comb_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS.CODE_COMBINATION_ID%TYPE
1251 INDEX BY BINARY_INTEGER;
1252 TYPE adjustment_type_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS. ADJUSTMENT_TYPE%TYPE
1253 INDEX BY BINARY_INTEGER;
1254 TYPE asset_id_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS.ASSET_ID%TYPE
1255 INDEX BY BINARY_INTEGER;
1256 TYPE dist_id_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS.DISTRIBUTION_ID%TYPE
1257 INDEX BY BINARY_INTEGER;
1258 TYPE period_counter_tbl_type IS TABLE OF IGI_IAC_ADJUSTMENTS.PERIOD_COUNTER%TYPE
1259 INDEX BY BINARY_INTEGER;
1260
1261 l_row_id rowed_type_tbl_type;
1262 l_adj_id adj_id_tbl_type;
1263 l_book_code book_type_tbl_type;
1264 l_code_comb_id code_comb_tbl_type;
1265 l_adj_type adjustment_type_tbl_type;
1266 l_asset_id asset_id_tbl_type;
1267 l_dist_id dist_id_tbl_type;
1268 l_period_ctr period_counter_tbl_type;
1269
1270 l_loop_count number;
1271
1272 BEGIN
1273 l_path := g_path||'Synchronize_Accounts';
1274 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Start of processing for synchronize accounts ');
1275 FOR l_get_transaction IN c_get_transaction LOOP
1276
1277 OPEN c_get_adjustments(l_get_transaction.adjustment_id);
1278 FETCH c_get_adjustments BULK COLLECT INTO
1279 l_row_id,
1280 l_adj_id,
1281 l_book_code,
1282 l_code_comb_id,
1283 l_adj_type,
1284 l_asset_id,
1285 l_dist_id,
1286 l_period_ctr;
1287 CLOSE c_get_adjustments;
1288
1289 FOR l_loop_count IN 1.. l_adj_id.count
1290 LOOP
1291
1292 l_rowid := l_row_id(l_loop_count);
1293 l_account_ccid := -1;
1294
1295 -- fecth the required accounts form the fa_dsitribution accounts for the
1296 --expense,cost and reserve
1297
1298 OPEN c_get_accounts(l_dist_id(l_loop_count));
1299 FETCH c_get_accounts INTO
1300 l_cost_ccid,
1301 l_expense_ccid,
1302 l_reserve_ccid,
1303 l_flex_num;
1304 IF (c_get_accounts%FOUND) THEN
1305 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Success in get account ccid from distribution accounts ');
1306 IF (l_adj_type(l_loop_count) = 'COST') THEN
1307 l_account_ccid := l_cost_ccid;
1308 ELSIF (l_adj_type(l_loop_count) = 'RESERVE') THEN
1309 l_account_ccid := l_reserve_ccid;
1310 ELSIF (l_adj_type(l_loop_count) = 'EXPENSE') THEN
1311 l_account_ccid := l_expense_ccid;
1312 END IF;
1313 END IF;
1314 CLOSE c_get_accounts;
1315 --- get the account from the fa_adjustmemts and fa_distribution_history if not found im
1316 -- fa_distribution_accounts.
1317
1318 IF (l_account_ccid = -1) THEN
1319 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,' Failed to get account ccid from distribution accounts *****');
1320 OPEN c_get_account_ccid(l_asset_id(l_loop_count),l_dist_id(l_loop_count),
1321 l_get_transaction.transaction_type_code ,
1322 l_adj_type(l_loop_count),
1323 l_get_transaction.transaction_header_id);
1324 FETCH c_get_account_ccid into l_account_ccid;
1325 IF c_get_account_ccid%NOTFOUND THEN
1326 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,' Failed to get COST/RESERVE ccid in synchronize accounts *****');
1327 l_account_ccid := -1;
1328 END IF;
1329 CLOSE c_get_account_ccid;
1330
1331 END IF;
1332
1333 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' asset_id' || l_asset_id(l_loop_count));
1334 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' distribution' || l_dist_id(l_loop_count));
1335 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' adjustment type ' ||l_adj_type(l_loop_count));
1336 -- get the account ccid for the adjustment
1337 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' account ccid ' || l_account_ccid);
1338 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' fetched ccid '|| l_code_comb_id(l_loop_count));
1339
1340 IF l_account_ccid = -1 THEN
1341
1342 -- IF the accounts are not found
1343 -- generate them using FA workflow
1344 -- get the category ID for the asset
1345
1346 SELECT a.category_id
1347 INTO l_category_id
1348 FROM fa_asset_history a
1349 ,fa_distribution_history d
1350 WHERE d.distribution_id = l_dist_id(l_loop_count)
1351 AND a.asset_id = d.asset_id
1352 AND d.date_effective >= a.date_effective
1353 AND d.date_effective < nvl(a.date_ineffective,sysdate);
1354
1355 -- Get the default accounts and ccids for a distributions
1356
1357 SELECT asset_cost_acct, deprn_expense_acct, deprn_reserve_acct,
1358 asset_cost_account_ccid, reserve_account_ccid
1359 INTO l_asset_cost_acct, l_dep_exp_acct, l_dep_res_acct,
1360 l_asset_cost_account_ccid ,l_reserve_account_ccid
1361 FROM fa_category_books
1362 WHERE book_type_code = p_book_type_code
1363 AND category_id = l_category_id;
1364
1365 -- get the flex_num and default CCID
1366
1367 SELECT accounting_flex_structure, flexbuilder_defaults_ccid
1368 into l_flex_num, l_default_ccid
1369 FROM fa_book_controls
1370 WHERE book_type_code = p_book_type_code ;
1371
1372 IF (l_adj_type(l_loop_count) = 'COST') THEN
1373 -- get the COST
1374 l_account_type := 'ASSET_COST';
1375 l_account_seg_val := l_asset_cost_acct;
1376 l_acct_ccid := l_asset_cost_account_ccid;
1377 ELSIF (l_adj_type(l_loop_count) ='RESERVE' ) THEN
1378 -- get the reserve account
1379 l_account_type := 'DEPRN_RSV';
1380 l_account_seg_val := l_dep_res_acct;
1381 l_acct_ccid := l_reserve_account_ccid ;
1382 ELSIF (l_adj_type(l_loop_count) ='EXPENSE' ) THEN
1383 -- get the expense account
1384 l_account_type := 'DEPRN_EXP' ;
1385 l_account_seg_val := l_dep_exp_acct;
1386 l_acct_ccid := l_code_comb_id(l_loop_count);
1387 END IF;
1388
1389 Select calendar_period_close_date
1390 into l_validation_date
1391 From fa_deprn_periods
1392 where book_type_code = p_book_type_code
1393 and period_counter = p_period_counter;
1394
1395 l_result := FAFLEX_PKG_WF.START_PROCESS(
1396 X_flex_account_type => l_account_type,
1397 X_book_type_code => p_book_type_code,
1398 X_flex_num => l_flex_num,
1399 X_dist_ccid => l_code_comb_id(l_loop_count),
1400 X_acct_segval => l_account_seg_val,
1401 X_default_ccid => l_default_ccid,
1402 X_account_ccid => l_acct_ccid,
1403 X_distribution_id => l_dist_id(l_loop_count),
1404 X_validation_date => l_validation_date,
1405 X_return_ccid => l_account_ccid);
1406 END IF;
1407
1408
1409 IF l_account_ccid = -1 THEN
1410 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_ACCOUNT_NOT_FOUND');
1411 FND_MESSAGE.SET_TOKEN('PROCESS','Revaluation',TRUE);
1412 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
1413 p_full_path => l_path,
1414 p_remove_from_stack => FALSE);
1415 fnd_file.put_line(fnd_file.log, fnd_message.get);
1416
1417 return FALSE;
1418 END IF;
1419
1420
1421 IF l_account_ccid <> (l_code_comb_id(l_loop_count)) THEN
1422 -- Update the ccid for the adjustment
1423 UPDATE igi_iac_adjustments
1424 SET code_combination_id= l_account_ccid
1425 WHERE rowid=l_rowid;
1426
1427 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Updated the adjusment with correct ccid' );
1428 END IF;
1429
1430 END LOOP;
1431 END LOOP;
1432 return TRUE;
1433
1434 EXCEPTION
1435 WHEN others THEN
1436 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
1437 return FALSE;
1438 END Synchronize_Accounts;
1439
1440 BEGIN
1441 --===========================FND_LOG.START=====================================
1442 g_state_level := FND_LOG.LEVEL_STATEMENT;
1443 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
1444 g_event_level := FND_LOG.LEVEL_EVENT;
1445 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
1446 g_error_level := FND_LOG.LEVEL_ERROR;
1447 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
1448 g_path := 'IGI.PLSQL.igiiarub.igi_iac_reval_utilities.';
1449 --===========================FND_LOG.END=====================================
1450
1451 END;
1452