[Home] [Help]
PACKAGE BODY: APPS.FII_CURR_CONV_MAINTAIN_PKG
Source
1 PACKAGE BODY FII_CURR_CONV_MAINTAIN_PKG AS
2 /* $Header: FIICRCVB.pls 120.0.12000000.1 2007/04/13 05:46:43 arcdixit noship $ */
3
4 G_PHASE VARCHAR2(120);
5 g_schema_name VARCHAR2(120);
6 g_retcode VARCHAR2(20) := NULL;
7 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
8
9 g_global_start_date DATE;
10 g_prim_currency VARCHAR2(15);
11 g_sec_currency VARCHAR2(15);
12 g_prim_rate_type VARCHAR2(30);
13 g_sec_rate_type VARCHAR2(30);
14 g_current_start_date DATE;
15
16 -- *******************************************************************
17 -- Initialize
18 -- *******************************************************************
19
20 PROCEDURE Initialize IS
21
22 l_dir VARCHAR2(160);
23
24 BEGIN
25
26 g_phase := 'Do set up for log file';
27
28 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
29 ------------------------------------------------------
30 -- Set default directory in case if the profile option
31 -- BIS_DEBUG_LOG_DIRECTORY is not set up
32 ------------------------------------------------------
33 if l_dir is NULL then
34 l_dir := FII_UTIL.get_utl_file_dir;
35 end if;
36
37 ----------------------------------------------------------------
38 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
39 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
40 -- the log files and output files are written to
41 ----------------------------------------------------------------
42 FII_UTIL.initialize('FII_CURR_CONV_MAINTAIN_PKG.log',
43 'FII_CURR_CONV_MAINTAIN_PKG.out', l_dir,
44 'FII_CURR_CONV_MAINTAIN_PKG');
45
46 g_phase := 'Check debug mode';
47
48 -- Determine if process will be run in debug mode
49 IF (NVL(G_Debug_Mode, 'N') <> 'N') THEN
50 FIIDIM_Debug := TRUE;
51 FII_UTIL.Write_Log ('Debug On');
52 ELSE
53 FIIDIM_Debug := FALSE;
54 FII_UTIL.Write_Log ('Debug Off');
55 END IF;
56
57 g_phase := 'Obtain FII schema name and user info';
58
59 -- Obtain FII schema name
60 g_schema_name := FII_UTIL.get_schema_name ('FII');
61
62 -- Obtain user ID, login ID and initialize package variables
63 FII_USER_ID := FND_GLOBAL.USER_ID;
64 FII_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
65
66 -- If some of the above values is not set, error out
67 IF (FII_User_Id is NULL OR
68 FII_Login_Id is NULL) THEN
69 FII_UTIL.Write_Log ('>>> Failed Intialization');
70 RAISE EX_fatal_err;
71 END IF;
72
73 g_phase := 'Obtain global start date, currencies and rate types';
74
75 g_global_start_date := bis_common_parameters.get_GLOBAL_START_DATE;
76 g_prim_currency := bis_common_parameters.get_currency_code;
77 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
78 g_prim_rate_type := bis_common_parameters.get_rate_type;
79 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
80
81 IF (FIIDIM_Debug) THEN
82 FII_UTIL.Write_Log ('g_global_start_date: '|| g_global_start_date);
83 FII_UTIL.Write_Log ('g_prim_currency: '|| g_prim_currency);
84 FII_UTIL.Write_Log ('g_sec_currency: '|| g_sec_currency);
85 FII_UTIL.Write_Log ('g_prim_rate_type: '|| g_prim_rate_type);
86 FII_UTIL.Write_Log ('g_sec_rate_type: '|| g_sec_rate_type);
87 END IF;
88
89 -- If some of the above values is not set, error out
90 IF (g_global_start_date is NULL OR
91 g_prim_currency is NULL OR
92 g_prim_rate_type is NULL) THEN
93 FII_UTIL.Write_Log ('>>> Failed Intialization');
94 RAISE EX_fatal_err;
95 END IF;
96
97 g_phase := 'Get the start date of the current period';
98
99 select start_date
100 into g_current_start_date
101 from FII_TIME_MONTH
102 where end_date >= g_global_start_date
103 and sysdate between start_date and end_date;
104
105 g_phase := 'Turn on trace if in debug mode';
106
107 -- Turn trace on if process is run in debug mode
108 IF (FIIDIM_Debug) THEN
109 -- Program running in debug mode, turning trace on
110 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
111 FII_UTIL.Write_Log ('Initialize: Set Trace On');
112 END IF;
113
114 Exception
115
116 When others then
117 FII_UTIL.Write_Log ('Unexpected error when calling Initialize...');
118 FII_UTIL.Write_Log ('G_PHASE: ' || G_PHASE);
119 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
120 RAISE;
121
122 END Initialize;
123
124 -- **************************************************************************
125 -- Detect missing rates
126 -- **************************************************************************
127
128 PROCEDURE Detect_Missing_Rates( p_try_num number ) IS
129
130 Cursor Dummy_Missing_Rates_Cur IS
131 SELECT
132 PERIOD_SET_NAME
133 , PERIOD_ID
134 , PERIOD_START_DATE
135 , PERIOD_END_DATE
136 , FROM_CURRENCY
137 , TO_CURRENCY
138 , CONVERSION_TYPE
139 , CONVERSION_RATE
140 , CURRENCY_TYPE
141 , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
142 FROM FII_CURR_CONV_RATES
143 WHERE 1=0;
144
145 TYPE CursorRef IS ref cursor RETURN Dummy_Missing_Rates_Cur%ROWTYPE;
146 Missing_Rates_Cur CursorRef;
147 Missing_Rates_Rec Dummy_Missing_Rates_Cur%ROWTYPE;
148
149 l_count NUMBER(15) := 0;
150 l_bool_ret BOOLEAN;
151 l_msg_name VARCHAR2(64);
152 l_tab_name VARCHAR2(64);
153
154 BEGIN
155
156 IF (FIIDIM_Debug) THEN
157 FII_MESSAGE.Func_Ent(
158 'FII_ORCL_RCODE_MAINTAIN_PKG.Detect_Missing_Rates');
159 END IF;
160
161 l_msg_name := 'FII_MISSING_RATE_' || p_try_num;
162 l_tab_name := 'FII_MISSING_RATE_TAB_' || p_try_num;
163
164 if (p_try_num = 1) then
165
166 Open Missing_Rates_Cur For
167 SELECT
168 PERIOD_SET_NAME
169 , PERIOD_ID
170 , PERIOD_START_DATE
171 , PERIOD_END_DATE
172 , FROM_CURRENCY
173 , TO_CURRENCY
174 , CONVERSION_TYPE
175 , CONVERSION_RATE
176 , CURRENCY_TYPE
177 , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
178 FROM FII_CURR_CONV_RATES_GT
179 MINUS
180 SELECT
181 PERIOD_SET_NAME
182 , PERIOD_ID
183 , PERIOD_START_DATE
184 , PERIOD_END_DATE
185 , FROM_CURRENCY
186 , TO_CURRENCY
187 , CONVERSION_TYPE
188 , CONVERSION_RATE
189 , CURRENCY_TYPE
190 , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
191 FROM FII_CURR_CONV_RATES
192 WHERE CONVERSION_RATE < 0;
193
194 elsif (p_try_num = 2) then
195
196 Open Missing_Rates_Cur For
197 SELECT
198 PERIOD_SET_NAME
199 , PERIOD_ID
200 , PERIOD_START_DATE
201 , PERIOD_END_DATE
202 , FROM_CURRENCY
203 , TO_CURRENCY
204 , CONVERSION_TYPE
205 , CONVERSION_RATE
206 , CURRENCY_TYPE
207 , least(PERIOD_START_DATE, g_current_start_date) CONVERSION_DATE
208 FROM FII_CURR_CONV_RATES
209 WHERE CONVERSION_RATE < 0;
210
211 else
212
213 RAISE EX_fatal_err;
214
215 end if;
216
217 LOOP
218
219 Fetch Missing_Rates_Cur Into Missing_Rates_Rec;
220 Exit When Missing_Rates_Cur%NOTFOUND;
221
222 l_count := l_count + 1;
223
224 if l_count = 1 then
225
226 FII_MESSAGE.write_log(msg_name => l_msg_name,
227 token_num => 0);
228 FII_MESSAGE.write_log(msg_name => 'FII_REFER_TO_OUTPUT',
229 token_num => 0);
230
231 FII_MESSAGE.write_output(msg_name => l_msg_name,
232 token_num => 0);
233 FII_MESSAGE.write_output(msg_name => l_tab_name,
234 token_num => 0);
235
236 end if;
237
238 FII_UTIL.Write_Output( rpad(' ', 4)
239 || rpad(Missing_Rates_Rec.PERIOD_ID, 14)
240 || rpad(Missing_Rates_Rec.CONVERSION_DATE, 20)
241 || rpad(Missing_Rates_Rec.FROM_CURRENCY, 18)
242 || rpad(Missing_Rates_Rec.TO_CURRENCY, 16)
243 || rpad(Missing_Rates_Rec.CONVERSION_TYPE, 19)
244 );
245
246 END LOOP;
247
248 Close Missing_Rates_Cur;
249
250 IF l_count > 0 THEN
251 l_bool_ret := FND_CONCURRENT.Set_Completion_Status(
252 status => 'WARNING',
253 message => 'Detected missing currency conversion rates.'
254 );
255 END IF;
256
257 IF (FIIDIM_Debug) THEN
258 FII_MESSAGE.Func_Succ (
259 'FII_ORCL_RCODE_MAINTAIN_PKG.Detect_Missing_Rates');
260 END IF;
261
262 Exception
263
264 When others then
265 FII_UTIL.Write_Log(
266 'Unexpected error when calling Detect_Missing_Rates.');
267 FII_UTIL.Write_Log('G_PHASE: ' || G_PHASE);
268 FII_UTIL.Write_Log('Error Message: '|| substr(sqlerrm,1,180));
269 RAISE;
270
271 END Detect_Missing_Rates;
272
273 -- **************************************************************************
274 -- This is the main procedure of the currency conversion program.
275 -- **************************************************************************
276
277 PROCEDURE Init_Load (errbuf OUT NOCOPY VARCHAR2,
278 retcode OUT NOCOPY VARCHAR2) IS
279
280 ret_val BOOLEAN := FALSE;
281
282 BEGIN
283
284 g_phase := 'Call Initialize';
285
286 Initialize;
287
288 IF (FIIDIM_Debug) THEN
289 FII_MESSAGE.Func_Ent(func_name =>
290 'FII_CURR_CONV_MAINTAIN_PKG.Init_Load');
291 END IF;
292
293 g_phase := 'Truncate table FII_CURR_CONV_RATES';
294
295 FII_UTIL.truncate_table ('FII_CURR_CONV_RATES', g_schema_name, g_retcode);
296
297 g_phase := 'Insert into FII_CURR_CONV_RATES';
298
299 insert into FII_CURR_CONV_RATES (
300 PERIOD_SET_NAME
301 , PERIOD_ID
302 , PERIOD_START_DATE
303 , PERIOD_END_DATE
304 , FROM_CURRENCY
305 , TO_CURRENCY
306 , CONVERSION_TYPE
307 , CONVERSION_RATE
308 , CURRENCY_TYPE
309 , CREATION_DATE
310 , CREATED_BY
311 , LAST_UPDATE_DATE
312 , LAST_UPDATED_BY
313 , LAST_UPDATE_LOGIN
314 )
315 select
316 'Gregorian'
317 , prd.MONTH_ID
318 , prd.START_DATE
319 , prd.END_DATE
320 , cfr.CURRENCY
321 , cto.currency_code
322 , cto.rate_type
323 , decode( cfr.CURRENCY, 'NA_EDW', 1,
324 GL_CURRENCY_API.get_rate_sql(
325 nvl(cfr.CURRENCY, '')
326 , cto.currency_code
327 , least(prd.START_DATE, g_current_start_date)
328 , cto.rate_type
329 )
330 )
331 , decode(cto.currency_code, g_prim_currency, 'P', 'S')
332 , SYSDATE
333 , FII_USER_ID
334 , SYSDATE
335 , FII_USER_ID
336 , FII_LOGIN_ID
337 from FII_TIME_MONTH prd
338 , HRI_CONVERT_FROM_CURRENCIES_V cfr
339 , ( select g_prim_currency currency_code, g_prim_rate_type rate_type
340 from dual
341 union all
342 select g_sec_currency currency_code, g_sec_rate_type rate_type
343 from dual
344 where g_sec_currency is not null
345 and g_sec_rate_type is not null
346 ) cto
347 where prd.end_date >= g_global_start_date;
348
349 IF (FIIDIM_Debug) THEN
350 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT ||
351 ' rows into FII_CURR_CONV_RATES');
352 END IF;
353
354 g_phase := 'Copy missing rates to FII_CURR_CONV_RATES_GT';
355
356 insert into FII_CURR_CONV_RATES_GT (
357 PERIOD_SET_NAME
358 , PERIOD_ID
359 , PERIOD_START_DATE
360 , PERIOD_END_DATE
361 , FROM_CURRENCY
362 , TO_CURRENCY
363 , CONVERSION_TYPE
364 , CONVERSION_RATE
365 , CURRENCY_TYPE
366 )
367 SELECT
368 PERIOD_SET_NAME
369 , PERIOD_ID
370 , PERIOD_START_DATE
371 , PERIOD_END_DATE
372 , FROM_CURRENCY
373 , TO_CURRENCY
374 , CONVERSION_TYPE
375 , CONVERSION_RATE
376 , CURRENCY_TYPE
377 FROM FII_CURR_CONV_RATES
378 WHERE CONVERSION_RATE < 0;
379
380 IF (FIIDIM_Debug) THEN
381 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT ||
382 ' rows into FII_CURR_CONV_RATES_GT');
383 END IF;
384
385 g_phase := 'Update FII_CURR_CONV_RATES';
386
387 update FII_CURR_CONV_RATES
388 set CONVERSION_RATE =
389 GL_CURRENCY_API.get_closest_rate_sql(
390 nvl(FROM_CURRENCY, '')
391 , TO_CURRENCY
392 , least(PERIOD_START_DATE, g_current_start_date)
393 , CONVERSION_TYPE
394 , least(PERIOD_START_DATE, g_current_start_date) - g_global_start_date
395 )
396 where CONVERSION_RATE < 0
397 and least(PERIOD_START_DATE, g_current_start_date) > g_global_start_date;
398
399 IF (FIIDIM_Debug) THEN
400 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT ||
401 ' rows in FII_CURR_CONV_RATES');
402 END IF;
403
404 g_phase := 'Detect missing rates in FII_CURR_CONV_RATES';
405
406 Detect_Missing_Rates(1);
407 Detect_Missing_Rates(2);
408
409 g_phase := 'Gather_table_stats for FII_CURR_CONV_RATES';
410
411 FND_STATS.gather_table_stats
412 (ownname => g_schema_name,
413 tabname => 'FII_CURR_CONV_RATES');
414
415 g_phase := 'Commit the change';
416
417 FND_CONCURRENT.Af_Commit;
418
419 IF (FIIDIM_Debug) THEN
420 FII_MESSAGE.Func_Succ(func_name =>
421 'FII_CURR_CONV_MAINTAIN_PKG.Init_Load');
422 END IF;
423
424 EXCEPTION
425
426 WHEN EX_fatal_err THEN
427 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
428 FII_UTIL.Write_Log ('FII_CURR_CONV_MAINTAIN_PKG.Init_Load: '||
429 'User defined error');
430 -- Rollback
431 FND_CONCURRENT.Af_Rollback;
432 FII_MESSAGE.Func_Fail(func_name =>
433 'FII_CURR_CONV_MAINTAIN_PKG.Init_Load');
434 retcode := sqlcode;
435 ret_val := FND_CONCURRENT.Set_Completion_Status
436 (status => 'ERROR', message => substr(sqlerrm,1,180));
437
438 WHEN OTHERS THEN
439 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
440 FII_UTIL.Write_Log (
441 'Other error in FII_CURR_CONV_MAINTAIN_PKG.Init_Load: ' ||
442 substr(sqlerrm,1,180));
443
444 -- Rollback
445 FND_CONCURRENT.Af_Rollback;
446 FII_MESSAGE.Func_Fail(func_name =>
447 'FII_CURR_CONV_MAINTAIN_PKG.Init_Load');
448 retcode := sqlcode;
449 ret_val := FND_CONCURRENT.Set_Completion_Status
450 (status => 'ERROR', message => substr(sqlerrm,1,180));
451
452 END Init_Load;
453
454 END FII_CURR_CONV_MAINTAIN_PKG;