eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGI_IAC_REVAL_ENGINE

Source


1 PACKAGE BODY IGI_IAC_REVAL_ENGINE AS
2 -- $Header: igiiareb.pls 120.10.12000000.1 2007/08/01 16:16:58 npandya ship $
3 
4 --===========================FND_LOG.START=====================================
5 
6 g_state_level NUMBER	     :=	FND_LOG.LEVEL_STATEMENT;
7 g_proc_level  NUMBER	     :=	FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER	     :=	FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER	     :=	FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER	     :=	FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER	     :=	FND_LOG.LEVEL_UNEXPECTED;
12 g_path        VARCHAR2(100)  := 'IGI.PLSQL.igiiareb.IGI_IAC_REVAL_ENGINE.';
13 
14 --===========================FND_LOG.END=======================================
15 
16 l_rec igi_iac_revaluation_rates%rowtype;  -- create this for quicker access via sql navigator
17 
18 procedure do_round ( p_amount in out NOCOPY number, p_book_type_code in varchar2) is
19     l_amount number     := p_amount;
20     l_amount_old number := p_amount;
21     l_path varchar2(150) := g_path||'do_round';
22 begin
23    IF IGI_IAC_COMMON_UTILS.Iac_Round(X_Amount => l_amount, X_Book => p_book_type_code)
24    THEN
25       p_amount := l_amount;
26    ELSE
27       p_amount := round( l_amount, 2);
28    END IF;
29 exception when others then
30   p_amount := l_amount_old;
31   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
32   Raise;
33 end;
34 
35 procedure do_round ( p_info in out NOCOPY IGI_IAC_TYPES.iac_reval_output_asset ) is
36      p_info_old IGI_IAC_TYPES.iac_reval_output_asset;
37      l_path varchar2(150) := g_path||'do_round';
38 begin
39      p_info_old := p_info;
40 
41      do_round( p_info.adjusted_cost   , p_info.book_type_code  )     ;
42      do_round( p_info.operating_acct  , p_info.book_type_code  )     ;
43      do_round( p_info.deprn_amount    , p_info.book_type_code  )     ;
44      do_round( p_info.backlog_deprn_reserve , p_info.book_type_code  )     ;
45      do_round( p_info.deprn_reserve    , p_info.book_type_code )     ;
46      do_round( p_info.general_fund     , p_info.book_type_code )     ;
47      do_round( p_info.reval_reserve    , p_info.book_type_code  )     ;
48      do_round( p_info.net_book_value   , p_info.book_type_code )  ;
49 
50 exception when others then
51   p_info := p_info_old;
52   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
53   Raise;
54 end;
55 
56 procedure display ( p_info in IGI_IAC_TYPES.iac_reval_output_asset ) IS
57      l_path varchar2(150) := g_path||'display';
58 begin
59      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'++++++++++++++++++++++++++++++++++++++++++++++++');
60      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Adjusted cost '||p_info.adjusted_cost);
61      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Operating Acc '||p_info.operating_acct);
62      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Deprn amount  '||p_info.deprn_amount );
63      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Backlog       '||p_info.backlog_deprn_reserve);
64      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Deprn Reserve '||p_info.deprn_reserve);
65      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    General Fund  '||p_info.general_fund);
66      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    Reval Reserve '||p_info.reval_reserve );
67      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    NBV           '||p_info.net_book_value );
68      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    current rate  '||p_info.current_reval_factor);
69      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'    cumulve rate  '||p_info.cumulative_reval_factor);
70      igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'++++++++++++++++++++++++++++++++++++++++++++++++');
71 end;
72 
73 Function  Calculations  ( p_iac_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params
74                         , p_second_set in boolean
75                         )
76 RETURN BOOLEAN IS
77       p_iac_reval_params_old IGI_IAC_TYPES.iac_reval_params;
78       l_reval_prev IGI_IAC_TYPES.iac_reval_output_asset;
79       l_reval_curr IGI_IAC_TYPES.iac_reval_output_asset;
80       l_reval_mvmt IGI_IAC_TYPES.iac_reval_output_asset;
81       l_curr_rate  number  := 0;
82       l_prev_rate  number  := 0;
83       l_tran_rate  number  := 0;
84       l_py_factor  number  := 0;
85       l_salvage_value_correction number :=0;
86       l_salvage_value_correction_ytd number :=0;
87       l_path varchar2(150) := g_path||'Calculations';
88 BEGIN
89       p_iac_reval_params_old := p_iac_reval_params;
90 
91       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin calculations');
92       if not p_second_set then
93           l_curr_rate := p_iac_reval_params.reval_curr_rate_info_first.cumulative_reval_factor;
94           l_prev_rate := p_iac_reval_params.reval_prev_rate_info.cumulative_reval_factor;
95 
96       else
97           l_curr_rate := p_iac_reval_params.reval_curr_rate_info_next.cumulative_reval_factor;
98           l_prev_rate := p_iac_reval_params.reval_prev_rate_info.cumulative_reval_factor;
99       end if;
100       l_tran_rate := l_curr_rate/ l_prev_rate;
101 
102       l_py_factor := p_iac_reval_params.fa_asset_info.deprn_periods_prior_year /
103                      p_iac_reval_params.fa_asset_info.deprn_periods_elapsed
104       ;
105       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+calculations : l_curr_rate '|| l_curr_rate);
106       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+calculations : l_prev_rate '|| l_prev_rate);
107       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+calculations : l_tran_rate '|| l_tran_rate);
108 
109       /* Apply the prev cumm rate to the fa info */
110       l_reval_prev := p_iac_reval_params.reval_input_asset;
111       l_reval_curr := p_iac_reval_params.reval_input_asset;
112 
113          igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' + calculations : deprn Resreve '|| 						p_iac_reval_params.fa_asset_info.deprn_reserve);
114          igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+ calculations : YTD  '|| 						p_iac_reval_params.fa_asset_info.Ytd_deprn);
115 
116       if (p_iac_reval_params.reval_control.first_time_flag) then
117           l_reval_prev.adjusted_cost           := ( l_prev_rate *  p_iac_reval_params.fa_asset_info.cost )
118                                                                 - p_iac_reval_params.fa_asset_info.cost ;
119           if l_curr_rate >= 1 and l_prev_rate >= 1 then
120              l_reval_prev.operating_acct          := 0;
121           else
122              l_reval_prev.operating_acct := l_reval_prev.adjusted_cost ;
123           end if;
124 
125           p_iac_reval_params.reval_asset_params.prev_ytd_deprn := ( l_prev_rate * p_iac_reval_params.fa_asset_info.ytd_deprn)
126                                               - p_iac_reval_params.fa_asset_info.ytd_deprn;
127            l_reval_prev.backlog_deprn_reserve   := (l_prev_rate - 1) *
128                                                      ( p_iac_reval_params.fa_asset_info.deprn_reserve -
129                                                        p_iac_reval_params.fa_asset_info.ytd_deprn)
130 
131                                                    /*) -
132                                                       ( p_iac_reval_params.fa_asset_info.deprn_reserve -
133                                                        p_iac_reval_params.fa_asset_info.ytd_deprn)*/ ;
134           if l_reval_prev.adjusted_cost >= 0 then
135               l_reval_prev.general_fund            := p_iac_reval_params.reval_asset_params.prev_ytd_deprn ;
136               l_reval_prev.reval_reserve           := l_reval_prev.adjusted_cost - l_reval_prev.backlog_deprn_reserve
137                                                        - l_reval_prev.general_fund ;
138           else
139              l_reval_prev.reval_reserve           := 0 ;
140              l_reval_prev.general_fund            := 0 ;
141           end if;
142 
143           l_reval_prev.deprn_reserve           := ( l_prev_rate * p_iac_reval_params.fa_asset_info.deprn_reserve)
144                                                   - p_iac_reval_params.fa_asset_info.deprn_reserve
145                                                   - l_reval_prev.backlog_deprn_reserve;
146 
147 
148          l_reval_prev.net_book_value          := l_reval_prev.adjusted_cost - l_reval_prev.deprn_reserve
149                                               - l_reval_prev.backlog_deprn_reserve;
150       else
151           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+calculations : not the first time');
152           p_iac_reval_params.reval_asset_params.prev_ytd_deprn := ( l_prev_rate * p_iac_reval_params.fa_asset_info.ytd_deprn)
153                                               - p_iac_reval_params.fa_asset_info.ytd_deprn;
154       end if;
155 
156       /* Display prev record */
157       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'++++++++++++++++++++++++++++++++++++++++++++++++');
158       if p_second_set then
159           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+++++++++++ SECOND SET : START +++++++++++');
160       else
161           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'++++++++++++ FIRST  SET : START +++++++++++');
162       end if;
163       Do_round( l_reval_prev );
164       Display( l_reval_prev );
165       /* Apply the curr cumm rate to the fa info */
166       l_reval_curr.adjusted_cost           := ( l_curr_rate *  p_iac_reval_params.fa_asset_info.cost )
167                                                             - p_iac_reval_params.fa_asset_info.cost;
168 
169      IF  p_iac_reval_params.fa_asset_info.period_counter_fully_reserved IS NOT NULL
170         AND p_iac_reval_params.reval_asset_params.period_counter > p_iac_reval_params.fa_asset_info.last_period_counter THEN
171       l_reval_curr.deprn_amount := 0;
172       l_reval_prev.deprn_amount := 0;
173      ELSE
174       l_reval_curr.deprn_amount            := ( l_curr_rate * p_iac_reval_params.fa_asset_info.deprn_amount)
175                                               - p_iac_reval_params.fa_asset_info.deprn_amount;
176      END IF;
177 
178      if p_second_set then
179           p_iac_reval_params.reval_asset_params.curr_ytd_deprn_next
180                                                := ( l_curr_rate * p_iac_reval_params.fa_asset_info.ytd_deprn)
181                                                   - p_iac_reval_params.fa_asset_info.ytd_deprn;
182           p_iac_reval_params.reval_asset_params.ytd_deprn_mvmt :=
183                                                 p_iac_reval_params.reval_asset_params.curr_ytd_deprn_next -
184                                                 p_iac_reval_params.reval_asset_params.prev_ytd_deprn ;
185      else
186           p_iac_reval_params.reval_asset_params.curr_ytd_deprn_first
187                                                := ( l_curr_rate * p_iac_reval_params.fa_asset_info.ytd_deprn)
188                                                   - p_iac_reval_params.fa_asset_info.ytd_deprn;
189           p_iac_reval_params.reval_asset_params.ytd_deprn_mvmt :=
190                                                 p_iac_reval_params.reval_asset_params.curr_ytd_deprn_first -
191                                                 p_iac_reval_params.reval_asset_params.prev_ytd_deprn ;
192      end if;
193 /* Backlog = movement in current revaluation * gross pyrs acc deprn
194 */
195      if l_py_factor <> 0 then
196           declare
197             l_blog number;
198           begin
199 
200             l_blog  := ( p_iac_reval_params.fa_asset_info.deprn_reserve -
201                                                        p_iac_reval_params.fa_asset_info.ytd_deprn) *
202                         ( l_curr_rate - l_prev_rate );
203             igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+current period backlog is '|| l_blog);
204             l_reval_curr.backlog_deprn_reserve      :=  l_blog + l_reval_prev.backlog_deprn_reserve;
205             igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+cumulative backlog is '|| 						l_reval_curr.backlog_deprn_reserve);
206           end;
207       else
208           l_reval_curr.backlog_deprn_reserve := 0;
209       end if;
210 
211       if l_curr_rate >= 1 and l_prev_rate >= 1 then
212             l_reval_curr.general_fund            := l_reval_prev.general_fund +
213                                       p_iac_reval_params.reval_asset_params.ytd_deprn_mvmt ;
214 
215             l_reval_curr.reval_reserve           := l_reval_curr.adjusted_cost - l_reval_curr.backlog_deprn_reserve
216                                               - l_reval_curr.general_fund - l_reval_curr.operating_acct;
217       else
218           l_reval_curr.reval_reserve          := 0;
219           l_reval_curr.operating_acct         := l_reval_curr.adjusted_cost - l_reval_curr.backlog_deprn_reserve
220                                                 - l_reval_curr.general_fund ;
221       end if;
222 
223           l_reval_curr.deprn_reserve           := ( l_curr_rate * p_iac_reval_params.fa_asset_info.deprn_reserve)
224                                               - p_iac_reval_params.fa_asset_info.deprn_reserve
225                                               - l_reval_curr.backlog_deprn_reserve;
226 
227       l_reval_curr.net_book_value          := l_reval_curr.adjusted_cost - l_reval_curr.deprn_reserve
228                                               - l_reval_curr.backlog_deprn_reserve;
229       Do_round( p_info => l_reval_curr );
230       /* put the differences in the asset mvmt table */
231       -- note accounting entries are created from this mvmt table --
232       if p_iac_reval_params.reval_control.first_time_flag then
233          igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+first time flag is set.');
234          l_reval_mvmt := l_reval_curr;
235       else
236           l_reval_mvmt.adjusted_cost           := l_reval_curr.adjusted_cost - l_reval_prev.adjusted_cost;
237           l_reval_mvmt.operating_acct          := l_reval_curr.operating_acct - l_reval_prev.operating_acct;
238           l_reval_mvmt.deprn_amount            := l_reval_curr.deprn_amount - l_reval_prev.deprn_amount;
239           l_reval_mvmt.backlog_deprn_reserve   := l_reval_curr.backlog_deprn_reserve
240                                                   - l_reval_prev.backlog_deprn_reserve;
241           l_reval_mvmt.deprn_reserve           := l_reval_curr.deprn_reserve - l_reval_prev.deprn_reserve;
242           l_reval_mvmt.general_fund            := l_reval_curr.general_fund - l_reval_prev.general_fund;
243           l_reval_mvmt.reval_reserve           := l_reval_curr.reval_reserve - l_reval_prev.reval_reserve;
244           l_reval_mvmt.net_book_value          := 0;
245       end if;
246       /* display results to the log file */
247       Do_round ( p_info => l_reval_mvmt );
248       if p_second_set then
249           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+++++++++++ SECOND SET :  REVAL MVMT ++++++++');
250       else
251           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+++++++++++ FIRST  SET :  REVAL MVMT ++++++++');
252       end if;
253 
254       Display ( p_info => l_reval_mvmt );
255       if p_second_set then
256           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+++++++++++ SECOND SET :  REVAL END ++++++++');
257       else
258           igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+++++++++++ FIRST  SET :  REVAL END ++++++++');
259       end if;
260       Display ( p_info => l_reval_curr );
261       if p_iac_reval_params.reval_control.first_time_flag then
262          p_iac_reval_params.reval_input_asset    := l_reval_prev ;
263       end if;
264       p_iac_reval_params.reval_output_asset      := l_reval_curr  ;
265       p_iac_reval_params.reval_output_asset.cumulative_reval_factor := l_curr_rate;
266       p_iac_reval_params.reval_output_asset.current_reval_factor    := l_tran_rate;
267       p_iac_reval_params.reval_output_asset_mvmt := l_reval_mvmt  ;
268       igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end calculations');
269       return TRUE;
270 
271 exception when others then
272   p_iac_reval_params := p_iac_reval_params_old;
273   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
274   return FALSE;
275 END;
276 
277 Function  First_set_calculations  ( p_iac_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params )
278 RETURN BOOLEAN IS
279     p_iac_reval_params_old IGI_IAC_TYPES.iac_reval_params;
280     l_path varchar2(150) := g_path||'First_set_calculations';
281 BEGIN
282 
283   p_iac_reval_params_old := p_iac_reval_params;
284 
285   igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin first set calculations');
286   IF NOT Calculations ( p_iac_reval_params => p_iac_reval_params, p_second_set => false )
287   THEN
288     igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'error first set calculations');
289     igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end  first set calculations');
290     return false;
291   END IF;
292   igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end  first set calculations');
293   return TRUE;
294 exception when others then
295   p_iac_reval_params := p_iac_reval_params_old;
296   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
297   return FALSE;
298 END;
299 
300 Function  Next_set_calculations  ( p_iac_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params )
301 RETURN BOOLEAN IS
302   p_iac_reval_params_old IGI_IAC_TYPES.iac_reval_params;
303   l_path varchar2(150) := g_path||'Next_set_calculations';
304 BEGIN
305 
306   p_iac_reval_params_old := p_iac_reval_params;
307 
308   IF NOT Calculations ( p_iac_reval_params => p_iac_reval_params, p_second_set => true )
309   THEN
310     igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'error next set calculations');
311     igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'end  next set calculations');
312     return false;
313   END IF;
314   return TRUE;
315 exception when others then
316   p_iac_reval_params := p_iac_reval_params_old;
317   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
318   return FALSE;
319 END;
320 /*
321 -- Get the rate information and then call Set_Calculations : FIRST  and  NEXT
322 */
323 
324 Function  Prepare_calculations (  p_iac_reval_params in out NOCOPY IGI_IAC_TYPES.iac_reval_params )
325 RETURN BOOLEAN IS
326   p_iac_reval_params_old IGI_IAC_TYPES.iac_reval_params;
327   l_path varchar2(150) := g_path||'Prepare_calculations';
328 BEGIN
329 
330   p_iac_reval_params_old := p_iac_reval_params;
331 
332    -- first split ratios (if revaluation is mixed!)
333    IF NOT IGI_IAC_REVAL_UTILITIES.split_rates
334           ( fp_asset_id                   => p_iac_reval_params.reval_asset_params.asset_id
335           , fp_book_type_code             => p_iac_reval_params.reval_asset_params.book_type_code
336           , fp_revaluation_id             => p_iac_reval_params.reval_asset_params.revaluation_id
337           , fp_period_counter             => p_iac_reval_params.reval_asset_params.period_counter
338           , fp_current_factor             => p_iac_reval_params.reval_asset_params.revaluation_rate
339           , fp_reval_type                 => p_iac_reval_params.reval_asset_rules.revaluation_type
340           , fp_first_time_flag            => p_iac_reval_params.reval_control.first_time_flag
341           , fp_mixed_scenario             => p_iac_reval_params.reval_control.mixed_scenario
342           , fp_reval_prev_rate_info       => p_iac_reval_params.reval_prev_rate_info
343           , fp_reval_curr_rate_info_first => p_iac_reval_params.reval_curr_rate_info_first
344           , fp_reval_curr_rate_info_next  => p_iac_reval_params.reval_curr_rate_info_next
345           )
346    THEN
347        igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Unable to split rates!');
348        return FALSE;
349    END IF;
350 
351    return TRUE;
352 exception when others then
353   p_iac_reval_params := p_iac_reval_params_old;
354   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
355   return FALSE;
356 END;
357 
358 function swap ( fp_reval_params1 IN IGI_IAC_TYPES.iac_reval_params
359               , fp_reval_params2 OUT NOCOPY IGI_IAC_TYPES.iac_reval_params
360                   )
361 return boolean is
362   fp_reval_params2_old IGI_IAC_TYPES.iac_reval_params;
363   l_path varchar2(150) := g_path||'swap';
364 begin
365 
366   if  fp_reval_params1.reval_control.mixed_scenario then
367     igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+intializing for the second set of calculations');
368     fp_reval_params2                                                  := fp_reval_params1;
369     fp_reval_params2.reval_control.first_time_flag                    := false;
370     fp_reval_params2.reval_input_asset                                := fp_reval_params1.reval_output_asset;
371     fp_reval_params2.reval_output_asset                               :=  fp_reval_params1.reval_output_asset;
372     fp_reval_params2.reval_output_asset_mvmt.net_book_value           := 0;
373     fp_reval_params2.reval_output_asset_mvmt.adjusted_cost            := 0;
374     fp_reval_params2.reval_output_asset_mvmt.operating_acct           := 0;
375     fp_reval_params2.reval_output_asset_mvmt.reval_reserve            := 0;
376     fp_reval_params2.reval_output_asset_mvmt.deprn_amount             := 0;
377     fp_reval_params2.reval_output_asset_mvmt.deprn_reserve            := 0;
378     fp_reval_params2.reval_output_asset_mvmt.backlog_deprn_reserve    := 0;
379     fp_reval_params2.reval_output_asset_mvmt.general_fund             := 0;
380     fp_reval_params2.reval_output_asset_mvmt.current_reval_factor     := 1;
381     fp_reval_params2.reval_output_asset_mvmt.cumulative_reval_factor  := 1;
382     fp_reval_params2.reval_prev_rate_info                             :=  fp_reval_params1.reval_curr_rate_info_first;
383     fp_reval_params2.reval_asset_params.prev_ytd_deprn := fp_reval_params1.reval_asset_params.prev_ytd_deprn ;
384     fp_reval_params2.reval_asset_params.prev_ytd_opacc := fp_reval_params1.reval_asset_params.prev_ytd_opacc;
385   end if;
386   return true;
387 exception when others then
388   fp_reval_params2 := fp_reval_params2_old;
389   igi_iac_debug_pkg.debug_unexpected_msg(l_path);
390   return FALSE;
391 end;
392 
393 END;
394 
395