[Home] [Help]
PACKAGE BODY: APPS.AS_PERIOD_RATES_REFRESH
Source
1 PACKAGE BODY AS_PERIOD_RATES_REFRESH as
2 /* $Header: asxrateb.pls 120.2 2005/06/14 01:32:14 appldev $ */
3
4 --
5 -- HISTORY
6 -- 03/20/2001 SOLIN Created
7 -- 05/20/2001 SOLIN Change table name from AS_RATES to
8 -- AS_PERIOD_RATES
9 -- Decide to use N*N currencies
10 --
11 -- FLOW
12 --
13 -- NOTES
14 -- The main package for the concurrent program "Refresh Mult-Currency
15 -- Conversion Rate(AS_PERIOD_RATES)"
16 --
17
18
19 /*-------------------------------------------------------------------------*
20 |
21 | PRIVATE CONSTANTS
22 |
23 *-------------------------------------------------------------------------*/
24 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_PERIOD_RATES_REFRESH';
25 G_FILE_NAME CONSTANT VARCHAR2(12):= 'asxrateb.pls';
26
27
28 /*-------------------------------------------------------------------------*
29 |
30 | PRIVATE DATATYPES
31 |
32 *-------------------------------------------------------------------------*/
33
34 /*-------------------------------------------------------------------------*
35 |
36 | PRIVATE VARIABLES
37 |
38 *-------------------------------------------------------------------------*/
39 g_debug_flag VARCHAR2(1);
40 g_request_id NUMBER;
41 g_user_id NUMBER;
42 g_prog_appl_id NUMBER;
43 g_prog_id NUMBER;
44 g_last_update_login NUMBER;
45
46 g_period_set_name VARCHAR2(15);
47 g_mc_date_mapping_type VARCHAR2(100);
48 g_max_roll_days NUMBER;
49
50 /*-------------------------------------------------------------------------*
51 |
52 | PRIVATE ROUTINES SPECIFICATION
53 |
54 *-------------------------------------------------------------------------*/
55 PROCEDURE fetch_profile_values;
56 PROCEDURE AS_DEBUG(p_module IN VARCHAR2, msg IN VARCHAR2);
57
58
59 /*-------------------------------------------------------------------------*
60 |
61 | PUBLIC ROUTINES
62 |
63 *-------------------------------------------------------------------------*/
64
65
66 /*-------------------------------------------------------------------------*
67 | PUBLIC ROUTINE
68 | Refresh_AS_PERIOD_RATES
69 |
70 | PURPOSE
71 | The main program to refresh AS_PERIOD_RATES table.
72 | Concurrent program to populate multi-currency conversion rate.
73 |
74 | NOTES
75 |
76 | HISTORY
77 | 03/20/2001 SOLIN Created
78 | 05/20/2001 SOLIN Changed table name from AS_RATES to AS_PERIOD_RATES
79 | 10/04/2001 SOLIN Add exception handler in case reporting currency
80 | is not defined in fnd_currencies.
81 *-------------------------------------------------------------------------*/
82
83 PROCEDURE Refresh_AS_PERIOD_RATES(
84 ERRBUF OUT NOCOPY VARCHAR2,
85 RETCODE OUT NOCOPY VARCHAR2,
86 p_debug_mode IN VARCHAR2,
87 p_trace_mode IN VARCHAR2)
88 IS
89 CURSOR c_get_dp_currency_s(c_period_set_name VARCHAR2) IS
90 SELECT TYPEMAP.CONVERSION_TYPE,
91 PERIOD.PERIOD_NAME, PERIOD.PERIOD_TYPE,
92 PERIOD.START_DATE CONVERSION_DATE,
93 LOOKUP1.LOOKUP_CODE FROM_CURRENCY,
94 LOOKUP2.LOOKUP_CODE TO_CURRENCY
95 FROM AS_MC_TYPE_MAPPINGS TYPEMAP,
96 AS_PERIOD_DAYS PERIOD,
97 FND_LOOKUP_VALUES LOOKUP1,
98 FND_LOOKUP_VALUES LOOKUP2
99 WHERE TYPEMAP.PERIOD_SET_NAME = c_period_set_name
100 AND TYPEMAP.PERIOD_TYPE = PERIOD.PERIOD_TYPE
101 AND PERIOD.PERIOD_SET_NAME = c_period_set_name
102 AND PERIOD.PERIOD_DAY = PERIOD.START_DATE
103 AND LOOKUP1.LOOKUP_TYPE = 'REPORTING_CURRENCY'
104 AND LOOKUP1.ENABLED_FLAG = 'Y'
105 AND (LOOKUP1.START_DATE_ACTIVE <= SYSDATE OR LOOKUP1.START_DATE_ACTIVE IS NULL)
106 AND (LOOKUP1.END_DATE_ACTIVE >= SYSDATE OR LOOKUP1.END_DATE_ACTIVE IS NULL)
107 -- ffang 081303, bug 3096884, checking language
108 AND LOOKUP1.language = userenv('LANG')
109 -- end ffang 081303, bug 3096884
110 AND LOOKUP2.LOOKUP_TYPE = 'REPORTING_CURRENCY'
111 AND LOOKUP2.ENABLED_FLAG = 'Y'
112 AND (LOOKUP2.START_DATE_ACTIVE <= SYSDATE OR LOOKUP2.START_DATE_ACTIVE IS NULL)
113 AND (LOOKUP2.END_DATE_ACTIVE >= SYSDATE OR LOOKUP2.END_DATE_ACTIVE IS NULL)
114 -- ffang 081303, bug 3096884, checking language
115 AND LOOKUP2.language = userenv('LANG')
116 -- end ffang 081303, bug 3096884
117 ;
118
119 CURSOR c_get_dp_currency_e(c_period_set_name VARCHAR2) IS
120 SELECT TYPEMAP.CONVERSION_TYPE,
121 PERIOD.PERIOD_NAME, PERIOD.PERIOD_TYPE,
122 PERIOD.END_DATE CONVERSION_DATE,
123 LOOKUP1.LOOKUP_CODE FROM_CURRENCY,
124 LOOKUP2.LOOKUP_CODE TO_CURRENCY
125 FROM AS_MC_TYPE_MAPPINGS TYPEMAP,
126 AS_PERIOD_DAYS PERIOD,
127 FND_LOOKUP_VALUES LOOKUP1,
128 FND_LOOKUP_VALUES LOOKUP2
129 WHERE TYPEMAP.PERIOD_SET_NAME = c_period_set_name
130 AND TYPEMAP.PERIOD_TYPE = PERIOD.PERIOD_TYPE
131 AND PERIOD.PERIOD_SET_NAME = c_period_set_name
132 AND PERIOD.PERIOD_DAY = PERIOD.END_DATE
133 AND LOOKUP1.LOOKUP_TYPE = 'REPORTING_CURRENCY'
134 AND LOOKUP1.ENABLED_FLAG = 'Y'
135 AND (LOOKUP1.START_DATE_ACTIVE <= SYSDATE OR LOOKUP1.START_DATE_ACTIVE IS NULL)
136 AND (LOOKUP1.END_DATE_ACTIVE >= SYSDATE OR LOOKUP1.END_DATE_ACTIVE IS NULL)
137 -- ffang 081303, bug 3096884, checking language
138 AND LOOKUP1.language = userenv('LANG')
139 -- end ffang 081303, bug 3096884
140 AND LOOKUP2.LOOKUP_TYPE = 'REPORTING_CURRENCY'
141 AND LOOKUP2.ENABLED_FLAG = 'Y'
142 AND (LOOKUP2.START_DATE_ACTIVE <= SYSDATE OR LOOKUP2.START_DATE_ACTIVE IS NULL)
143 AND (LOOKUP2.END_DATE_ACTIVE >= SYSDATE OR LOOKUP2.END_DATE_ACTIVE IS NULL)
144 -- ffang 081303, bug 3096884, checking language
145 AND LOOKUP2.language = userenv('LANG')
146 -- end ffang 081303, bug 3096884
147 ;
148
149 ddl_curs INTEGER;
150 l_denominator NUMBER;
151 l_numerator NUMBER;
152 l_precision NUMBER;
153 l_mau NUMBER;
154 l_rate NUMBER;
155 l_status_flag NUMBER;
156 l_period_rate_id NUMBER;
157 l_status BOOLEAN;
158
159 l_conversion_type VARCHAR2(10);
160 l_period_name VARCHAR2(15);
161 l_period_type VARCHAR2(15);
162 l_conversion_date DATE;
163 l_from_currency VARCHAR2(15);
164 l_to_currency VARCHAR2(15);
165 l_fnd_status VARCHAR2(2);
166 l_industry VARCHAR2(2);
167 l_oracle_schema VARCHAR2(32) := 'OSM';
168 l_schema_return BOOLEAN;
169 l_module CONSTANT VARCHAR2(255) := 'as.plsql.prate.Refresh_AS_PERIOD_RATES';
170 BEGIN
171 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_fnd_status, l_industry, l_oracle_schema);
172
173 g_debug_flag := p_debug_mode;
174
175 AS_DEBUG(l_module, '*** ASXRATES starts ***');
176
177 fetch_profile_values;
178
179 -- truncate AS_PERIOD_RATES
180 ddl_curs := dbms_sql.open_cursor;
181 dbms_sql.parse(ddl_curs,'TRUNCATE TABLE ' || l_oracle_schema || '.AS_PERIOD_RATES drop storage',
182 dbms_sql.native);
183 dbms_sql.close_cursor(ddl_curs);
184
185 -- For period rate
186 IF g_mc_date_mapping_type='S'
187 THEN
188 OPEN c_get_dp_currency_s(g_period_set_name);
189 ELSE -- 'E'
190 OPEN c_get_dp_currency_e(g_period_set_name);
191 END IF;
192
193 LOOP
194 IF g_mc_date_mapping_type='S'
195 THEN
196 FETCH c_get_dp_currency_s INTO
197 l_conversion_type, l_period_name, l_period_type,
198 l_conversion_date, l_from_currency, l_to_currency;
199 EXIT WHEN c_get_dp_currency_s%NOTFOUND;
200 ELSE -- 'E'
201 FETCH c_get_dp_currency_e INTO
202 l_conversion_type, l_period_name, l_period_type,
203 l_conversion_date, l_from_currency, l_to_currency;
204 EXIT WHEN c_get_dp_currency_e%NOTFOUND;
205 END IF;
206
207 AS_DEBUG(l_module, l_conversion_type || ' ' || l_period_name
208 || ' ' || l_period_type || ' ' || l_conversion_date
209 || ' ' || l_from_currency || ' ' || l_to_currency);
210
211 BEGIN
212 l_status_flag := 0;
213 gl_currency_api.get_closest_triangulation_rate(
214 l_from_currency, l_to_currency,
215 l_conversion_date, l_conversion_type,
216 g_max_roll_days, l_denominator, l_numerator,
217 l_rate);
218
219 EXCEPTION
220 WHEN others THEN
221 IF SQLCODE=1
222 THEN
223 AS_DEBUG(l_module, l_from_currency || ' to ' || l_to_currency
224 || ' rate not found.');
225 l_denominator := 1.0;
226 l_numerator := 0.0;
227 l_rate := 0.0;
228 l_status_flag := 1;
229 END IF;
230 END;
231 -- AS_DEBUG(l_from_currency || ' to ' || l_to_currency || ':' || l_rate);
232
233 BEGIN
234 SELECT precision,
235 NVL(minimum_accountable_unit, power(10,-1*precision))
236 INTO l_precision, l_mau
237 FROM fnd_currencies
238 WHERE currency_code = l_to_currency;
239
240 -- AS_DEBUG('precision:' || l_precision || ' mau:' || l_mau);
241
242 SELECT AS_PERIOD_RATES_S.NEXTVAL INTO l_period_rate_id FROM DUAL;
243
244 -- Insertion for daily rate
245 INSERT INTO AS_PERIOD_RATES(
246 PERIOD_RATE_ID, FROM_CURRENCY, TO_CURRENCY,
247 CONVERSION_TYPE, CONVERSION_DATE, CONVERSION_RATE,
248 NUMERATOR_RATE, DENOMINATOR_RATE, PRECISION,
249 MINIMUM_ACCOUNTABLE_UNIT,
250 CONVERSION_STATUS_FLAG, PERIOD_TYPE, PERIOD_NAME,
251 PERIOD_SET_NAME,
252 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
253 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
254 PROGRAM_APPLICATION_ID, PROGRAM_ID,
255 PROGRAM_UPDATE_DATE)
256 VALUES( l_period_rate_id, l_from_currency, l_to_currency,
257 l_conversion_type, l_conversion_date,
258 l_rate, l_numerator, l_denominator, l_precision,
259 l_mau, l_status_flag, l_period_type,
260 l_period_name, g_period_set_name,
261 SYSDATE, g_user_id, SYSDATE, g_user_id, g_last_update_login,
262 g_request_id, g_prog_appl_id, g_prog_id, SYSDATE);
263
264 COMMIT;
265
266 EXCEPTION
267 WHEN others THEN
268 AS_DEBUG(l_module, 'Currency ' || l_to_currency || ' is not defined.');
269 END;
270 END LOOP;
271
272 IF g_mc_date_mapping_type='S'
273 THEN
274 CLOSE c_get_dp_currency_s;
275 ELSE -- 'E'
276 CLOSE c_get_dp_currency_e;
277 END IF;
278
279 DBMS_STATS.GATHER_TABLE_STATS ('OSM','AS_PERIOD_RATES', degree=>4, estimate_percent => 99, granularity => 'GLOBAL', cascade=>TRUE);
280 COMMIT;
281 EXCEPTION
282 WHEN others THEN
283 AS_DEBUG(l_module, 'Exception: others in Refresh_AS_PERIOD_RATES');
284 AS_DEBUG(l_module, 'SQLCODE ' || to_char(SQLCODE) ||
285 ' SQLERRM ' || substr(SQLERRM, 1, 100));
286
287 errbuf := SQLERRM;
288 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
289 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
290 END Refresh_AS_PERIOD_RATES;
291
292 /*-------------------------------------------------------------------------*
293 |
294 | PRIVATE ROUTINES
295 |
296 *-------------------------------------------------------------------------*/
297
298 /*-------------------------------------------------------------------------*
299 | PRIVATE ROUTINE
300 | fetch_profile_values
301 |
302 | PURPOSE
303 | Fetch necessary profile values
304 |
305 | NOTES
306 |
307 |
308 | HISTORY
309 | 03/20/2001 SOLIN Created
310 *-------------------------------------------------------------------------*/
311 Procedure fetch_profile_values
312 IS
313 l_module CONSTANT VARCHAR2(255) := 'as.plsql.prate.fetch_profile_values';
314 Begin
315 IF FND_PROFILE.Value('AS_FORECAST_CALENDAR') IS NULL THEN
316 as_debug(l_module, 'Value of AS_FORECAST_CALENDAR is not set');
317 ELSE
318 g_period_set_name := FND_PROFILE.Value('AS_FORECAST_CALENDAR') ;
319 END IF;
320 as_debug(l_module, 'Profile AS_FORECAST_CALENDAR: ' || g_period_set_name);
321
322 IF FND_PROFILE.Value('AS_MC_DATE_MAPPING_TYPE') IS NULL THEN
323 as_debug(l_module, 'Value of AS_MC_DATE_MAPPING_TYPE is not set');
324 ELSE
325 g_mc_date_mapping_type := FND_PROFILE.Value('AS_MC_DATE_MAPPING_TYPE') ;
326 END IF;
327 as_debug(l_module, 'Profile AS_MC_DATE_MAPPING_TYPE: ' || g_mc_date_mapping_type);
328
329 IF FND_PROFILE.Value('AS_MC_MAX_ROLL_DAYS') IS NULL THEN
330 as_debug(l_module, 'Value of AS_MC_MAX_ROLL_DAYS is not set');
331 ELSE
332 g_max_roll_days := FND_PROFILE.Value('AS_MC_MAX_ROLL_DAYS') ;
333 END IF;
334 as_debug(l_module, 'Profile AS_MC_MAX_ROLL_DAYS: ' || g_max_roll_days);
335
336 -- IF FND_PROFILE.Value('AS_MC_DAILY_CONVERSION_TYPE') IS NULL THEN
337 -- as_debug('Value of AS_MC_DAILY_CONVERSION_TYPE is not set');
338 -- ELSE
339 -- g_daily_conversion_type := FND_PROFILE.Value('AS_MC_DAILY_CONVERSION_TYPE');
340 -- END IF;
341 -- as_debug('Profile AS_MC_DAILY_CONVERSION_TYPE: ' || g_daily_conversion_type);
342
343 -- IF FND_PROFILE.Value('AS_PREFERRED_CURRENCY') IS NULL THEN
344 -- as_debug('Value of AS_PREFERRED_CURRENCY is not set');
345 -- ELSE
346 -- g_preferred_currency := FND_PROFILE.Value('AS_PREFERRED_CURRENCY');
347 -- END IF;
348 -- as_debug('Profile AS_PREFERRED_CURRENCY: ' || g_preferred_currency);
349
350 g_request_id := TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
351 g_user_id := NVL(TO_NUMBER(fnd_profile.value('USER_ID')), -1);
352 g_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
353 g_prog_id := to_number(fnd_profile.value('CONC_PROGRAM_ID'));
354 g_last_update_login := NVL(TO_NUMBER(fnd_profile.value('CONC_LOGIN_ID')), -1);
355
356 as_debug(l_module, 'request_id: ' || g_request_id);
357 as_debug(l_module, 'user_id: ' || g_user_id);
358 as_debug(l_module, 'program_application_id: ' || g_prog_appl_id);
359 as_debug(l_module, 'program_id: ' || g_prog_id);
360 as_debug(l_module, 'last_update_login: ' || g_last_update_login);
361
362 End fetch_profile_values;
363
364 /*-------------------------------------------------------------------------*
365 | PRIVATE ROUTINE
366 | As_Debug
367 |
368 | PURPOSE
369 | write debug message
370 |
371 | NOTES
372 |
373 |
374 | HISTORY
375 | 03/20/2001 SOLIN Created
376 *-------------------------------------------------------------------------*/
377
378 PROCEDURE AS_DEBUG(p_module in VARCHAR2, msg in VARCHAR2)
379 IS
380 l_length NUMBER;
381 l_start NUMBER := 1;
382 l_substring VARCHAR2(255);
383 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
384 BEGIN
385 IF g_debug_flag = 'Y' THEN
386 IF l_debug THEN
387 AS_UTILITY_PVT.Debug_Message(p_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, msg);
388 ELSE
389 -- chop the message to 255 long
390 l_length := length(msg);
391 WHILE l_length > 255 LOOP
392 l_substring := substr(msg, l_start, 255);
393 FND_FILE.PUT_LINE(FND_FILE.LOG, l_substring);
394 -- dbms_output.put_line(l_substring);
395
396 l_start := l_start + 255;
397 l_length := l_length - 255;
398 END LOOP;
399
400 l_substring := substr(msg, l_start);
401 FND_FILE.PUT_LINE(FND_FILE.LOG,l_substring);
402 -- dbms_output.put_line(l_substring);
403 END IF;
404 END IF;
405 EXCEPTION
406 WHEN others THEN
407 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in AS_DEBUG');
408 FND_FILE.PUT_LINE(FND_FILE.LOG,
409 'SQLCODE ' || to_char(SQLCODE) ||
410 ' SQLERRM ' || substr(SQLERRM, 1, 100));
411 END As_Debug;
412
413 END AS_PERIOD_RATES_REFRESH;
414