[Home] [Help]
PACKAGE BODY: APPS.FTP_IRC_ADI_MIGRATE
Source
1 PACKAGE BODY ftp_irc_adi_migrate AS
2 --$Header: ftpmgrtb.pls 120.4 2006/05/15 05:29:10 appldev noship $
3
4 /**********************
5 -- Package Constants
6 **********************/
7 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
8 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
9 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
10 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
11 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
12 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
13
14 c_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
15 c_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
16
17 /***************************************************************************
18 Desc : Migrates Parameter Rates type of Interest Rate Codes.
19 Pgmr : Raghuram K Nanda
20 Date : 8-Mar-2005
21 History: 28-Mar-2005
22 Pgmr : Karen added: update interfece table status with 'F' when migration failed
23 **************************************************************************/
24 PROCEDURE migrateparameters (
25 errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY VARCHAR2,
27 p_int_rate_code IN NUMBER
28 )
29 IS
30
31 TYPE t_eff_date_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.EFFECTIVE_DATE%TYPE;
32 TYPE t_mean_rev_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.MEAN_REVERSION_SPEED%TYPE;
33 TYPE t_long_rr_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.LONG_RUN_RATE%TYPE;
34 TYPE t_vol_merton_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.VOLATILITY_MERTON%TYPE;
35 TYPE t_vol_vasicek_tbl IS TABLE OF FTP_IRC_ADI_PARAM_T.VOLATILITY_VASICEK%TYPE;
36
37 l_eff_date_tbl t_eff_date_tbl;
38 l_mean_rev_tbl t_mean_rev_tbl;
39 l_long_rr_tbl t_long_rr_tbl;
40 l_vol_merton_tbl t_vol_merton_tbl;
41 l_vol_vasicek_tbl t_vol_vasicek_tbl;
42
43 l_block CONSTANT VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.migrateparameters';
44 BEGIN
45
46 FEM_ENGINES_PKG.TECH_MESSAGE(
47 p_severity => c_log_level_2,
48 p_module => l_block||'.interest_rate_code',
49 p_msg_text => p_int_rate_code
50 );
51
52 --Read the values
53 select EFFECTIVE_DATE,MEAN_REVERSION_SPEED,
54 LONG_RUN_RATE,VOLATILITY_MERTON,VOLATILITY_VASICEK
55 bulk collect into l_eff_date_tbl,l_mean_rev_tbl,l_long_rr_tbl,
56 l_vol_merton_tbl,l_vol_vasicek_tbl
57 from FTP_IRC_ADI_PARAM_T where
58 INTEREST_RATE_CODE = p_int_rate_code;
59
60 FOR i in 1..l_mean_rev_tbl.COUNT LOOP
61 BEGIN
62 -- if present, update it
63 UPDATE FTP_IRC_TS_PARAM_HIST SET
64 MEAN_REVERSION_SPEED = l_mean_rev_tbl(i),
65 LONG_RUN_RATE = l_long_rr_tbl(i),
66 VOLATILITY_MERTON = l_vol_merton_tbl(i),
67 VOLATILITY_VASICEK = l_vol_vasicek_tbl(i)
68 WHERE EFFECTIVE_DATE = l_eff_date_tbl(i) AND
69 INTEREST_RATE_CODE = p_int_rate_code;
70
71 IF (SQL%ROWCOUNT = 0)
72 THEN
73 INSERT INTO FTP_IRC_TS_PARAM_HIST(EFFECTIVE_DATE,INTEREST_RATE_CODE,
74 MEAN_REVERSION_SPEED,LONG_RUN_RATE,VOLATILITY_MERTON,VOLATILITY_VASICEK,
75 RATE_DATA_SOURCE_CODE,IS_VALID_FLG,LAST_MODIFIED_DATE,CREATION_DATE,
76 CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE)
77 VALUES(l_eff_date_tbl(i),p_int_rate_code,l_mean_rev_tbl(i),l_long_rr_tbl(i),
78 l_vol_merton_tbl(i),l_vol_vasicek_tbl(i),1,1,sysdate,sysdate,1,1,sysdate);
79
80 --Karen added for testing
81 update FTP_IRC_ADI_PARAM_T set STATUS ='INSERT'
82 where EFFECTIVE_DATE = l_eff_date_tbl(i)
83 AND INTEREST_RATE_CODE = p_int_rate_code;
84 ELSE
85 -- it has updated. so set the status to UPDATE
86 --Karen added for testing
87 update FTP_IRC_ADI_PARAM_T set STATUS ='UPDATE'
88 where EFFECTIVE_DATE = l_eff_date_tbl(i)
89 AND INTEREST_RATE_CODE = p_int_rate_code;
90
91 END IF;
92 EXCEPTION
93 when others then
94 --Karen added
95 --set status to 'F'
96 update FTP_IRC_ADI_PARAM_T set STATUS ='FTP_PARAM_MIGRATE_ERR' where EFFECTIVE_DATE = l_eff_date_tbl(i)
97 AND INTEREST_RATE_CODE = p_int_rate_code;
98
99 --put message on the message stack for calling program to handle
100 FEM_ENGINES_PKG.Put_Message(
101 p_app_name => 'FTP',
102 p_msg_name => 'FTP_PARAM_MIGRATE_ERR',
103 p_token1 => 'EFF_DATE',
104 p_value1 => l_eff_date_tbl(i)
105 );
106 END;
107 END LOOP;
108
109
110 FEM_ENGINES_PKG.TECH_MESSAGE(
111 p_severity => c_log_level_2,
112 p_module => l_block,
113 p_msg_text => 'Successfully inserted/updated rows: '||l_mean_rev_tbl.COUNT
114 );
115
116 --commit the data
117 COMMIT;
118
119 retcode := c_true;
120
121 --remove the successful insert/update entries from the interface table
122 deleteparameters (
123 errbuf => errbuf,
124 retcode => retcode,
125 p_int_rate_code => p_int_rate_code
126 );
127
128 EXCEPTION
129 when others then
130
131 --put message on the message stack for calling program to handle
132 FEM_ENGINES_PKG.Put_Message(
133 p_app_name => 'FTP',
134 p_msg_name => 'FTP_UNEXP_ERR',
135 p_token1 => 'SQLERRM',
136 p_value1 => sqlerrm
137 );
138 retcode := c_false;
139 END migrateparameters;
140
141 /***************************************************************************
142 Desc : Deletes Parameter Rates type of Interest Rate Codes.
143 Pgmr : Raghuram K Nanda
144 Date : 8-Mar-2005
145 **************************************************************************/
146 PROCEDURE deleteparameters (
147 errbuf OUT NOCOPY VARCHAR2,
148 retcode OUT NOCOPY VARCHAR2,
149 p_int_rate_code IN NUMBER
150 )
151 IS
152 l_block CONSTANT VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.deleteparameters';
153 BEGIN
154
155 FEM_ENGINES_PKG.TECH_MESSAGE(
156 p_severity => c_log_level_2,
157 p_module => l_block||'.interest_rate_code',
158 p_msg_text => p_int_rate_code
159 );
160
161 DELETE FROM FTP_IRC_ADI_PARAM_T WHERE
162 INTEREST_RATE_CODE = p_int_rate_code and STATUS IN ('INSERT','UPDATE');
163
164 FEM_ENGINES_PKG.TECH_MESSAGE(
165 p_severity => c_log_level_2,
166 p_module => l_block,
167 p_msg_text => 'Successfully deleted rows: '||SQL%ROWCOUNT
168 );
169
170 --commit the data
171 COMMIT;
172 retcode := c_true;
173 EXCEPTION
174 when others then
175
176 --put message on the message stack for calling program to handle
177 FEM_ENGINES_PKG.Put_Message(
178 p_app_name => 'FTP',
179 p_msg_name => 'FTP_UNEXP_ERR',
180 p_token1 => 'SQLERRM',
181 p_value1 => sqlerrm
182 );
183 retcode := c_false;
184 END deleteparameters;
185
186 /***************************************************************************
187 Desc : PL/SQL Table to be used in migrate hist rates procedure.
188 Pgmr : Bobby Mathew, Thanks to Shintaro Okuda
189 Date : 20060223
190 Bug : 5048839
191 **************************************************************************/
192
193 FUNCTION FTP_MULTI_TABLE_F RETURN FTP_MULTI_TABLE PIPELINED IS
194 BEGIN
195 FOR i IN 1..100 LOOP
196 PIPE ROW(i);
197 END LOOP;
198 RETURN;
199 END;
200
201
202 /***************************************************************************
203 Desc : Migrates Historical Rates type of Interest Rate Code.
204 Pgmr : Raghuram K Nanda
205 Date : 8-Mar-2005
206 **************************************************************************/
207 PROCEDURE migratehistrates (
208 errbuf OUT NOCOPY VARCHAR2,
209 retcode OUT NOCOPY VARCHAR2,
210 p_int_rate_code IN NUMBER
211 )
212 IS
213 l_select_stmt varchar2(1000);
214 l_curr_date DATE := NULL;
215 l_int_term FTP_IRC_RATE_TERMS.INTEREST_RATE_TERM%TYPE;
216 l_int_mult FTP_IRC_RATE_TERMS.INTEREST_RATE_TERM_MULT%TYPE;
217 l_indx NUMBER;
218
219 TYPE t_int_rates_tbl IS TABLE OF FTP_IRC_ADI_RATE_T.INTEREST_RATE1%TYPE;
220 TYPE t_int_terms_tbl IS TABLE OF FTP_IRC_RATE_TERMS.INTEREST_RATE_TERM%TYPE;
221 TYPE t_int_mults_tbl IS TABLE OF FTP_IRC_RATE_TERMS.INTEREST_RATE_TERM_MULT%TYPE;
222 TYPE t_eff_date_tbl IS TABLE OF FTP_IRC_ADI_RATE_T.EFFECTIVE_DATE%TYPE;
223
224 l_int_rates_tbl t_int_rates_tbl;
225 l_int_terms_tbl t_int_terms_tbl;
226 l_int_mults_tbl t_int_mults_tbl;
227 l_eff_date_tbl t_eff_date_tbl;
228
229 l_block CONSTANT VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.migratehistrates';
230
231 BEGIN
232 FEM_ENGINES_PKG.TECH_MESSAGE(
233 p_severity => c_log_level_2,
234 p_module => l_block||'.interest_rate_code',
235 p_msg_text => p_int_rate_code
236 );
237
238 --Get the term and its multipliers
239 select INTEREST_RATE_TERM_MULT,INTEREST_RATE_TERM
240 bulk collect into l_int_mults_tbl,l_int_terms_tbl
241 from FTP_IRC_RATE_TERMS
242 where INTEREST_RATE_CODE = p_int_rate_code
243 --Bobby20050719 - UI sorts terms points based on actual length of the term - - Bug 4494361
244 order by INTEREST_RATE_TERM * decode(INTEREST_RATE_TERM_MULT,'D',1, 'M',30.5, 'Y',365);
245
246 --Get the interest rates in row fashion
247 --Bobby20050719 - modified to include 100 Interest Rate columns
248 --Bobby20050831 - Bug 4582713 modified to include 100 Interest Rate columns
249 select EFFECTIVE_DATE,INT_RATE
250 bulk collect into l_eff_date_tbl,l_int_rates_tbl
251 from (
252 select INTEREST_RATE_CODE,EFFECTIVE_DATE,decode( COLUMN_VALUE,
253 1,INTEREST_RATE1, 2,INTEREST_RATE2,
254 3,INTEREST_RATE3, 4,INTEREST_RATE4,
255 5,INTEREST_RATE5, 6,INTEREST_RATE6, 7,INTEREST_RATE7,
256 8,INTEREST_RATE8, 9,INTEREST_RATE9, 10,INTEREST_RATE10,
257 11,INTEREST_RATE11, 12,INTEREST_RATE12,
258 13,INTEREST_RATE13, 14,INTEREST_RATE14,
259 15,INTEREST_RATE15, 16,INTEREST_RATE16, 17,INTEREST_RATE17,
260 18,INTEREST_RATE18, 19,INTEREST_RATE19, 20,INTEREST_RATE20,
261 21,INTEREST_RATE21, 22,INTEREST_RATE22,
262 23,INTEREST_RATE23, 24,INTEREST_RATE24,
263 25,INTEREST_RATE25, 26,INTEREST_RATE26, 27,INTEREST_RATE27,
264 28,INTEREST_RATE28, 29,INTEREST_RATE29, 30,INTEREST_RATE30,
265 31,INTEREST_RATE31, 32,INTEREST_RATE32,
266 33,INTEREST_RATE33, 34,INTEREST_RATE34,
267 35,INTEREST_RATE35, 36,INTEREST_RATE36, 37,INTEREST_RATE37,
268 38,INTEREST_RATE38, 39,INTEREST_RATE39, 40,INTEREST_RATE40,
269 41,INTEREST_RATE41, 42,INTEREST_RATE42,
270 43,INTEREST_RATE43, 44,INTEREST_RATE44,
271 45,INTEREST_RATE45, 46,INTEREST_RATE46, 47,INTEREST_RATE47,
272 48,INTEREST_RATE48, 49,INTEREST_RATE49, 50,INTEREST_RATE50,
273 51,INTEREST_RATE51, 52,INTEREST_RATE52,
274 53,INTEREST_RATE53, 54,INTEREST_RATE54,
275 55,INTEREST_RATE55, 56,INTEREST_RATE56, 57,INTEREST_RATE57,
276 58,INTEREST_RATE58, 59,INTEREST_RATE59, 60,INTEREST_RATE60,
277 61,INTEREST_RATE61, 62,INTEREST_RATE62,
278 63,INTEREST_RATE63, 64,INTEREST_RATE64,
279 65,INTEREST_RATE65, 66,INTEREST_RATE66, 67,INTEREST_RATE67,
280 68,INTEREST_RATE68, 69,INTEREST_RATE69, 70,INTEREST_RATE70,
281 71,INTEREST_RATE71, 72,INTEREST_RATE72,
282 73,INTEREST_RATE73, 74,INTEREST_RATE74,
283 75,INTEREST_RATE75, 76,INTEREST_RATE76, 77,INTEREST_RATE77,
284 78,INTEREST_RATE78, 79,INTEREST_RATE79, 80,INTEREST_RATE80,
285 81,INTEREST_RATE81, 82,INTEREST_RATE82,
286 83,INTEREST_RATE83, 84,INTEREST_RATE84,
287 85,INTEREST_RATE85, 86,INTEREST_RATE86, 87,INTEREST_RATE87,
288 88,INTEREST_RATE88, 89,INTEREST_RATE89, 90,INTEREST_RATE90,
289 91,INTEREST_RATE91, 92,INTEREST_RATE92,
290 93,INTEREST_RATE93, 94,INTEREST_RATE94,
291 95,INTEREST_RATE95, 96,INTEREST_RATE96, 97,INTEREST_RATE97,
292 98,INTEREST_RATE98, 99,INTEREST_RATE99, 100,INTEREST_RATE100
293 ) int_rate
294 from FTP_IRC_ADI_RATE_T, TABLE(FTP_MULTI_TABLE_F())
295 --Bobby20050719 - Interest rate should be in the same order as it interface table - Bug 4494361
296 where INTEREST_RATE_CODE = p_int_rate_code order by EFFECTIVE_DATE,COLUMN_VALUE )
297 where INT_RATE is not null;
298
299
300 l_indx := 0;
301
302 FOR i in 1..l_int_rates_tbl.COUNT LOOP
303 BEGIN
304
305 IF (l_eff_date_tbl(i) <> l_curr_date OR l_curr_date IS NULL) THEN
306 l_curr_date := l_eff_date_tbl(i);
307 l_indx := 1;
308 ELSE
309 l_indx := l_indx + 1;
310 END IF;
311
312 l_int_term := l_int_terms_tbl(l_indx);
313 l_int_mult := l_int_mults_tbl(l_indx);
314 -- if present, update it
315 UPDATE FTP_IRC_RATE_HIST SET
316 INTEREST_RATE = l_int_rates_tbl(i)
317 where INTEREST_RATE_TERM = l_int_term and
318 INTEREST_RATE_TERM_MULT = l_int_mult and
319 EFFECTIVE_DATE = l_eff_date_tbl(i) and
320 INTEREST_RATE_CODE = p_int_rate_code;
321
322 IF (SQL%ROWCOUNT = 0)
323 THEN
324 insert into FTP_IRC_RATE_HIST(EFFECTIVE_DATE,INTEREST_RATE_CODE,
325 INTEREST_RATE_TERM,INTEREST_RATE_TERM_MULT,INTEREST_RATE,
326 RATE_DATA_SOURCE_CODE,LAST_MODIFIED_DATE,CREATION_DATE,CREATED_BY,
327 LAST_UPDATED_BY,LAST_UPDATE_DATE )
328 values(l_eff_date_tbl(i),p_int_rate_code,l_int_term,
329 l_int_mult,l_int_rates_tbl(i),1,sysdate,sysdate,1,1,sysdate );
330
331 --Karen added for testing
332 update FTP_IRC_ADI_RATE_T set STATUS ='INSERT'
333 where EFFECTIVE_DATE = l_eff_date_tbl(i)
334 AND INTEREST_RATE_CODE = p_int_rate_code;
335 ELSE
336 -- it has updated. so set the status to UPDATE
337 --Karen added for testing
338 update FTP_IRC_ADI_RATE_T set STATUS ='UPDATE'
339 where EFFECTIVE_DATE = l_eff_date_tbl(i)
340 AND INTEREST_RATE_CODE = p_int_rate_code;
341 END IF;
342 EXCEPTION
343 when others then
344
345 --put message on the message stack for calling program to handle
346 FEM_ENGINES_PKG.Put_Message(
347 p_app_name => 'FTP',
348 p_msg_name => 'FTP_HIST_MIGRATE_ERR',
349 p_token1 => 'EFF_DATE',
350 p_value1 => l_eff_date_tbl(i)
351 );
352 END;
353 END LOOP;
354
355
356 FEM_ENGINES_PKG.TECH_MESSAGE(
357 p_severity => c_log_level_2,
358 p_module => l_block,
359 p_msg_text => 'Successfully inserted/updated rows: '||l_int_terms_tbl.COUNT
360 );
361
362 --commit the data
363 COMMIT;
364 retcode := c_true;
365
366 --remove the successful insert/update entries from the interface table
367 deletehistrates (
368 errbuf => errbuf,
369 retcode => retcode,
370 p_int_rate_code => p_int_rate_code
371 );
372
373 EXCEPTION
374 when others then
375
376 --put message on the message stack for calling program to handle
377 FEM_ENGINES_PKG.Put_Message(
378 p_app_name => 'FTP',
379 p_msg_name => 'FTP_UNEXP_ERR',
380 p_token1 => 'SQLERRM',
381 p_value1 => sqlerrm
382 );
383 retcode := c_false;
384
385 END migratehistrates;
386
387 /***************************************************************************
388 Desc : Deletes Historical Rates type of Interest Rate Code.
389 Pgmr : Raghuram K Nanda
390 Date : 8-Mar-2005
391 **************************************************************************/
392 PROCEDURE deletehistrates (
393 errbuf OUT NOCOPY VARCHAR2,
394 retcode OUT NOCOPY VARCHAR2,
395 p_int_rate_code IN NUMBER
396 )
397 IS
398 l_block CONSTANT VARCHAR2(80) := 'ftp.plsql.ftp_irc_adi_migrate.deletehistrates';
399 BEGIN
400
401 FEM_ENGINES_PKG.TECH_MESSAGE(
402 p_severity => c_log_level_2,
403 p_module => l_block||'.interest_rate_code',
404 p_msg_text => p_int_rate_code
405 );
406
407 DELETE FROM FTP_IRC_ADI_RATE_T WHERE INTEREST_RATE_CODE = p_int_rate_code and
408 STATUS IN ('INSERT','UPDATE');
409
410 FEM_ENGINES_PKG.TECH_MESSAGE(
411 p_severity => c_log_level_2,
412 p_module => l_block,
413 p_msg_text => 'Successfully deleted rows: '||SQL%ROWCOUNT
414 );
415
416 --commit the data
417 COMMIT;
418 retcode := c_true;
419 EXCEPTION
420 when others then
421
422 --put message on the message stack for calling program to handle
423 FEM_ENGINES_PKG.Put_Message(
424 p_app_name => 'FTP',
425 p_msg_name => 'FTP_UNEXP_ERR',
426 p_token1 => 'SQLERRM',
427 p_value1 => sqlerrm
428 );
429 retcode := c_false;
430
431 END deletehistrates;
432
433 END FTP_IRC_ADI_MIGRATE;