1 PACKAGE BODY igi_iac_rebase_pkg AS
2 -- $Header: igiiacrb.pls 120.5.12000000.1 2007/08/01 16:13:39 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.igiiacrb.igi_iac_rebase_pkg.';
13
14 --===========================FND_LOG.END=====================================
15
16 PROCEDURE do_rebase(
17 errbuf OUT NOCOPY varchar2 ,
18 retcode OUT NOCOPY number,
19 price_index_id IN number, --- bug : 2504403 varchar2 changed this to number as now the concurrent process is passing price_index_id ,
20 calendar IN varchar2,
21 period_name IN varchar2,
22 new_price_index_value IN number) IS
23
24
25 /* Cursors Definition */
26 /* Cursor to obtain the price_index_id for the given price_index_name */
27
28 Cursor c_get_price_index(p_price_index_name igi_iac_price_indexes.price_index_name%type) is
29 Select price_index_id from igi_iac_price_indexes where
30 price_index_name = p_price_index_name;
31
32
33 /* Cursor to obtain cal_price_index_link_id */
34
35 Cursor c_get_link_id (p_price_index_id igi_iac_price_indexes.price_index_id%type) is
36 Select cal_price_index_link_id from igi_iac_cal_price_indexes where
37 calendar_type = calendar and
38 price_index_id = p_price_index_id;
39
40
41 /* Cursor to obtain the current_price_index_values for all the periods of the calendar */
42
43 Cursor c_get_curr_price_ind_val(p_cal_price_link_id igi_iac_cal_price_indexes.cal_price_index_link_id%type) is
44 Select iciv.*,iciv.rowid from igi_iac_cal_idx_values iciv where
45 cal_price_index_link_id = p_cal_price_link_id;
46
47 /* Cursor to obtain current price index value for the rebasing period */
48
49 Cursor c_get_rebase_period(p_link_id igi_iac_cal_idx_values.cal_price_index_link_id%type,
50 p_calendar igi_iac_cal_price_indexes.calendar_type%type,
51 p_period fa_calendar_periods.period_name%type) is
52 Select iciv.current_price_index_value from
53 igi_iac_cal_idx_values iciv ,fa_calendar_periods fcp
54 where fcp.start_date = iciv.date_from and
55 fcp.end_date = iciv.date_to and
56 fcp.calendar_type = p_calendar and
57 fcp.period_name = p_period and
58 iciv.cal_price_index_link_id = p_link_id;
59
60 /* Select iciv.current_price_index_value from igi_iac_cal_idx_values iciv , fa_calendar_periods fcp where
61 fcp.calendar_type = calendar and
62 fcp.period_name = period_name and
63 iciv.date_from = fcp.start_date and
64 iciv.date_to = fcp.end_date and
65 cal_price_index_link_id = p_link_id;*/
66
67 /* Cursor for getting the rows in IGI_IAC_CAL_PRICE_INDEXES for updation using TBH */
68
69 Cursor c_get_cal_price_indexes(p_link_id igi_iac_cal_price_indexes.cal_price_index_link_id%type) is
70 Select icpi.rowid , icpi.* from igi_iac_cal_price_indexes icpi
71 where icpi.cal_price_index_link_id = p_link_id;
72
73
74 /* Cursor to get the period name - for display in the log file only */
75 Cursor c_get_period_name(l_date_from igi_iac_cal_idx_values.date_from%type,
76 l_date_to igi_iac_cal_idx_values.date_to%type) is
77 Select fap.period_name from fa_calendar_periods fap
78 where start_date =l_date_from and end_date = l_date_to and
79 calendar_type= calendar;
80
81 /* Cursor to get the precision */
82 Cursor cur_get_precision(p_sob_id gl_sets_of_books.set_of_books_id%type) Is
83 select curr.precision
84 from fnd_currencies curr, gl_sets_of_books sob
85 where curr.currency_code = sob.currency_code
86 and sob.set_of_books_id = p_sob_id;
87
88 l_link_id igi_iac_cal_price_indexes.cal_price_index_link_id%type;
89 l_price_index_id igi_iac_price_indexes.price_index_id%type;
90 l_calendar igi_iac_cal_price_indexes.calendar_type%type;
91 l_period fa_calendar_periods.period_name%type;
92 l_default_index_val igi_iac_cal_idx_values.current_price_index_value%type default 9999.99;
93
94
95
96 /* This stores the current price index value of the period which is passed as the parameter to this concurrent process */
97 l_factor_price_index igi_iac_cal_idx_values.current_price_index_value%type;
98
99 /* This stores the current price index value for each period */
100 l_current_price_idx_rec c_get_curr_price_ind_val%rowtype;
101 l_new_curr_price_idx_val igi_iac_cal_idx_values.current_price_index_value%type;
102 l_period_name_rec c_get_period_name%rowtype;
103
104
105 l_cal_price_indexes_rec c_get_cal_price_indexes%rowtype;
106
107 l_rowcount number;
108 l_sob_id gl_sets_of_books.set_of_books_id%type;
109 l_precision number;
110
111 IGI_IAC_INDEX_PERIOD_NOT_FOUND Exception;
112 IGI_IAC_PRICE_INDEX_ZERO Exception;
113 IGI_IAC_NOT_ENABLED Exception;
114
115 Begin
116 /* Check whether the IAC Option is enabled */
117 if NOT igi_gen.is_req_installed('IAC') then
118 raise IGI_IAC_NOT_ENABLED;
119 END IF;
120
121 /* Check whether the new price_index_value is zero - raise exception if so */
122 IF (new_price_index_value =0) THEN
123 raise IGI_IAC_PRICE_INDEX_ZERO;
124 END IF;
125
126
127 l_calendar:=calendar;
128 l_period:=period_name;
129
130 /* Get the price_index_id */
131 /*open c_get_price_index(price_index_name);
132 fetch c_get_price_index into l_price_index_id;
133 close c_get_price_index;*/
134
135 l_price_index_id:=price_index_id;
136 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase' ,' Price index id' ||l_price_index_id);
137 /* Get the cal_price_index_link_id */
138 open c_get_link_id(price_index_id);
139 fetch c_get_link_id into l_link_id;
140 close c_get_link_id;
141
142 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase',' link id' ||l_link_id);
143 /* Check whether periods of the given calendar are linked to the price index in the igi_iac_cal_idx_values tables */
144 open c_get_curr_price_ind_val(l_link_id);
145 fetch c_get_curr_price_ind_val into l_current_price_idx_rec;
146 IF (c_get_curr_price_ind_val%NOTFOUND) THEN
147 raise IGI_IAC_INDEX_PERIOD_NOT_FOUND;
148 END IF;
149 close c_get_curr_price_ind_val;
150
151 /* To obtain the current_price_index value of the rebase period */
152 open c_get_rebase_period(l_link_id,l_calendar,l_period);
153 fetch c_get_rebase_period into l_factor_price_index;
154 close c_get_rebase_period;
155
156
157
158 /* Log Messages */
159 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
160 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','Calendar : ' || calendar);
161 -- fnd_file.put_line(fnd_file.log,'Price Index : ' || price_index_name);
162 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','Rebase Period : ' || period_name);
163 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase' ,'New Price Index value : ' || new_price_index_value);
164 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
165
166
167
168 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase' ,'|Period |Old |Current|');
169 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','| |Index |Index |');
170 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','| |Value |Value |');
171 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
172 /* End of Log Messages */
173
174
175 /* To get the precision of the functional currency */
176 fnd_profile.get('GL_SET_OF_BKS_ID', l_sob_id);
177 open cur_get_precision(l_sob_id);
178 fetch cur_get_precision into l_precision;
179 close cur_get_precision;
180
181 /* Update the price indexes of all periods of the calendar using the factor */
182 for l_current_price_idx_rec in c_get_curr_price_ind_val(l_link_id) loop
183 /* Get the period name */
184 open c_get_period_name(l_current_price_idx_rec.date_from,l_current_price_idx_rec.date_to);
185 fetch c_get_period_name into l_period_name_rec;
186 close c_get_period_name;
187 IF ( (l_period_name_rec.period_name <> period_name) and
188 (l_current_price_idx_rec.current_price_index_value <> l_default_index_val) ) THEN /* Bug No :2392641 sowsubra */
189
190 l_new_curr_price_idx_val:=
191 new_price_index_value * (l_current_price_idx_rec.current_price_index_value/l_factor_price_index);
192 /* Display the price index value details in the log file */
193 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','|'||rpad(l_period_name_rec.period_name,30,' ')||'|'||rpad(l_current_price_idx_rec.current_price_index_value,7,' ')
194 ||'|'||rpad(l_new_curr_price_idx_val,7,' ')||'|');
195
196 ELSIF (l_period_name_rec.period_name = period_name) THEN
197 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
198 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase', period_name ||' is the rebasing period. So no calculation is done .');
199 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','New price index value = ' || new_price_index_value);
200 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
201 l_new_curr_price_idx_val:= new_price_index_value;
202
203 /* Bug No :2392641 sowsubra start */
204 ELSIF (l_current_price_idx_rec.current_price_index_value = l_default_index_val) THEN
205 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase', l_period_name_rec.period_name ||'has the default price index value : ' ||
206 l_default_index_val || '.So No rebase done for this period .');
207 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','New price index value = ' ||l_default_index_val);
208 l_new_curr_price_idx_val:=l_default_index_val ;
209 /* Bug No :2392641 sowsubra end */
210 END IF;
211
212 l_new_curr_price_idx_val := round(l_new_curr_price_idx_val,l_precision);
213
214
215
216 /* call to update the igi_iac_cal_idx_values using the TBH */
217
218 igi_iac_cal_idx_values_pkg.update_row (
219 x_mode => 'R',
220 x_rowid => l_current_price_idx_rec.rowid,
221 x_cal_price_index_link_id => l_current_price_idx_rec.cal_price_index_link_id,
222 x_date_from => l_current_price_idx_rec.date_from,
223 x_date_to => l_current_price_idx_rec.date_to,
224 x_original_price_index_value => l_current_price_idx_rec.original_price_index_value,
225 x_current_price_index_value => l_new_curr_price_idx_val
226 );
227
228
229
230 END LOOP;
231 igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'do_rebase','------------------------------------------------');
232 open c_get_cal_price_indexes(l_link_id);
233 fetch c_get_cal_price_indexes into l_cal_price_indexes_rec;
234 close c_get_cal_price_indexes;
235
236 /*Update the igi_iac_cal_price_indexes table to record the rebase period name and price_index details */
237 igi_iac_cal_price_indexes_pkg.update_row (
238 x_mode => 'R',
239 x_rowid => l_cal_price_indexes_rec.rowid,
240 x_cal_price_index_link_id => l_cal_price_indexes_rec.cal_price_index_link_id,
241 x_price_index_id => l_cal_price_indexes_rec.price_index_id,
242 x_calendar_type => l_cal_price_indexes_rec.calendar_type,
243 x_previous_rebase_period_name => period_name,
244 x_previous_rebase_date => trunc(sysdate),
245 x_previous_rebase_index_before => l_factor_price_index,
246 x_previous_rebase_index_after => new_price_index_value
247 );
248
249
250 EXCEPTION
251 WHEN IGI_IAC_NOT_ENABLED THEN
252 fnd_message.set_name('IGI','IGI_IAC_NOT_INSTALLED');
253 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'do_rebase',FALSE);
254 Errbuf:=fnd_message.get;
255 retcode :=2;
256 WHEN IGI_IAC_PRICE_INDEX_ZERO THEN
257 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
258 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_rebase_pkg');
259 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Error : Price index value 0 cannot be processed ');
260 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'do_rebase',FALSE);
261
262 retcode :=2;
263 Errbuf := fnd_message.get;
264 WHEN ZERO_DIVIDE THEN
265 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
266 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_rebase_pkg');
267 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Division By Zero ');
268 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'do_rebase',FALSE);
269 retcode :=2;
270 errbuf:= fnd_message.get;
271 WHEN IGI_IAC_INDEX_PERIOD_NOT_FOUND THEN
272 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
273 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_iac_rebase_pkg');
274 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Price Index not linked to the calendar periods ');
275 igi_iac_debug_pkg.debug_other_msg(g_error_level,g_path||'do_rebase',FALSE);
276 retcode:=2;
277 errbuf:= fnd_message.get;
278 END do_rebase;
279 END igi_iac_rebase_pkg;
[Home] [Help]