[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_CURRENCY
Source
1 PACKAGE BODY msd_dem_collect_currency AS
2 /* $Header: msddemccb.pls 120.5.12000000.2 2007/09/25 06:13:13 syenamar noship $ */
3
4 PROCEDURE get_min_max_date(l_min_date OUT NOCOPY DATE,
5 l_max_date OUT NOCOPY DATE) AS
6
7 l_stmt VARCHAR2(1000);
8
9 BEGIN
10
11 l_stmt := 'select max(datet), min(datet) from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'INPUTS');
12
13 EXECUTE IMMEDIATE l_stmt
14 INTO l_max_date,
15 l_min_date;
16
17 EXCEPTION
18 WHEN others THEN
19 msd_dem_common_utilities.log_message(SUBSTR(sqlerrm, 1, 150));
20 msd_dem_common_utilities.log_debug(SUBSTR(sqlerrm, 1, 150));
21
22 END;
23
24 PROCEDURE process_currency(retcode OUT NOCOPY VARCHAR2,
25 p_currency_code IN VARCHAR2,
26 p_base_currency_code IN VARCHAR2,
27 p_from_date IN DATE,
28 p_to_date IN DATE,
29 l_base_curr IN VARCHAR2,
30 g_dblink IN VARCHAR2)
31
32 AS
33
34 l_lookup_value VARCHAR2(200);
35 l_schema_name VARCHAR2(200);
36
37 CURSOR verify_entities_inuse IS
38 SELECT 1
39 FROM msd_dem_entities_inuse
40 WHERE internal_name = p_currency_code
41 and ebs_entity = 'Currency';
42
43 type c_get_new_currency_real_value IS ref CURSOR;
44 get_new_currency_real_value c_get_new_currency_real_value;
45 l_stmt_new_currency_real_value VARCHAR2(2000);
46
47 type new_currency_rectype IS record(real_value_id NUMBER, real_table VARCHAR2(500));
48
49 new_currency new_currency_rectype;
50
51 type c_get_old_currency_real_value IS ref CURSOR;
52 get_old_currency_real_value c_get_old_currency_real_value;
53 l_stmt_old_currency_real_value VARCHAR2(2000);
54
55 type c_get_component IS ref CURSOR;
56 get_component c_get_component;
57 l_stmt_get_component VARCHAR2(2000);
58
59 type c_get_component_sop IS ref CURSOR;
60 get_component_sop c_get_component_sop;
61 l_stmt_get_component_sop VARCHAR2(2000);
62
63 type c_get_seeded_unit is ref cursor;
64 get_seeded_unit c_get_seeded_unit;
65 l_stmt_get_seeded_unit varchar2(2000);
66
67 l_get_seeded_unit varchar2(250);
68
69 l_verify_entities_inuse NUMBER;
70
71 l_component_id NUMBER;
72 l_component_id_sop NUMBER;
73 l_cur_count number;
74
75 l_stmt VARCHAR2(2000);
76
77 BEGIN
78
79 l_stmt_get_seeded_unit := 'select real_value from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') ||
80 ' where real_value = ''' || p_currency_code || '''';
81
82 open get_seeded_unit for l_stmt_get_seeded_unit;
83 fetch get_seeded_unit into l_get_seeded_unit;
84 close get_seeded_unit;
85
86 OPEN verify_entities_inuse;
87 FETCH verify_entities_inuse
88 INTO l_verify_entities_inuse;
89 CLOSE verify_entities_inuse;
90
91 IF l_verify_entities_inuse IS NOT NULL THEN
92
93 l_stmt_old_currency_real_value := 'select real_value_id, real_table from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' where real_value = ''' || p_currency_code || '''';
94
95 OPEN get_old_currency_real_value FOR l_stmt_old_currency_real_value;
96 FETCH get_old_currency_real_value
97 INTO new_currency;
98 CLOSE get_old_currency_real_value;
99
100 -- msd_dem_common_utilities.log_message('Currency Code: '||p_currency_code ||' already exists.');
101
102 ELSIF l_verify_entities_inuse IS NULL THEN
103
104 if l_get_seeded_unit is not null then
105 msd_dem_common_utilities.log_message('Seeded Display Unit with name ' || p_currency_code || ' exist in Demantra. This Currency will not be created');
106 msd_dem_common_utilities.log_debug('Seeded Display Unit with name ' || p_currency_code || ' exist in Demantra. This Currency will not be created');
107 retcode := 1;
108 return;
109 end if;
110
111 msd_dem_common_utilities.log_message('Creating Currency : ' || p_currency_code);
112
113 l_stmt_new_currency_real_value := 'select real_value_id, real_table ' || ' from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' where real_value_id in '
114 || ' (select distinct real_value_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' minus ' || ' select distinct real_value_id from '
115 || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ' )' || ' and lower(real_value) like ''%ebscurrency%'' and rownum < 2';
116 msd_dem_common_utilities.log_debug(l_stmt_new_currency_real_value);
117
118 OPEN get_new_currency_real_value FOR l_stmt_new_currency_real_value;
119 FETCH get_new_currency_real_value
120 INTO new_currency;
121
122 IF get_new_currency_real_value % NOTFOUND THEN
123 msd_dem_common_utilities.log_message('Cannot create new currency' || ' as no more empty dummy currency exists.');
124 msd_dem_common_utilities.log_debug('Cannot create new currency' || ' as no more empty dummy currency exists.');
125 CLOSE get_new_currency_real_value;
126 retcode := 1;
127 RETURN;
128 END IF;
129
130 CLOSE get_new_currency_real_value;
131
132 l_stmt := 'insert into msd_dem_entities_inuse(
133 ebs_entity
134 ,demantra_entity
135 ,internal_name
136 ,table_name
137 ,column_name
138 ,last_update_date
139 ,last_updated_by
140 ,creation_date
141 ,created_by
142 ,last_update_login
143 ) values
144 (
145 ''Currency''
146 ,''INDEX''
147 ,:1
148 ,:2
149 ,NULL
150 ,:4
151 ,:5
152 ,:6
153 ,:7
154 ,:8
155 )';
156
157 EXECUTE IMMEDIATE l_stmt USING p_currency_code, new_currency.real_table, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.user_id;
158 l_stmt := 'update ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || '
159 set real_value = :1
160 where real_value_id = :2';
161
162 EXECUTE IMMEDIATE l_stmt USING p_currency_code,
163 new_currency.real_value_id;
164
165 l_stmt_get_component := 'select dcm_product_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = '''
166 || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
167
168 OPEN get_component FOR l_stmt_get_component;
169 FETCH get_component
170 INTO l_component_id;
171 CLOSE get_component;
172
173 l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || '
174 (dcm_product_id
175 ,real_value_id
176 )
177 (
178 select :1, :2 from dual
179 )';
180 EXECUTE IMMEDIATE l_stmt USING l_component_id,
181 new_currency.real_value_id;
182
183
184
185 l_stmt_get_component_sop := 'select dcm_product_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = '''
186 || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
187
188 OPEN get_component_sop FOR l_stmt_get_component_sop;
189 FETCH get_component_sop
190 INTO l_component_id_sop;
191 CLOSE get_component_sop;
192
193 l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || '
194 (dcm_product_id
195 ,real_value_id
196 )
197 (
198 select :1, :2 from dual
199 )';
200 EXECUTE IMMEDIATE l_stmt USING l_component_id_sop,
201 new_currency.real_value_id;
202
203 l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
204 ' (display_units_id, real_value_id) ' ||
205 ' (select display_units_id, :1 from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' du, msd_dem_entities_inuse mdei ' ||
206 ' where mdei.ebs_entity = ''PRL'' and mdei.internal_name = du.display_units)';
207
208 msd_dem_common_utilities.log_debug(l_stmt);
209
210 execute immediate l_stmt using new_currency.real_value_id;
211
212 END IF;
213
214 msd_dem_common_utilities.log_message('Collecting Currency : ' || p_currency_code);
215 msd_dem_common_utilities.log_debug('Collecting Currency : ' || p_currency_code);
216
217 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
218
219 l_stmt := 'delete from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where index_date between :1 and :2 ';
220
221 msd_dem_common_utilities.log_debug('Bind Variables: ');
222 msd_dem_common_utilities.log_debug('From Date: ' || p_from_date);
223 msd_dem_common_utilities.log_debug('To Date: ' || p_to_date);
224 msd_dem_common_utilities.log_debug('Delete statement for currency: ' || l_stmt);
225
226 EXECUTE IMMEDIATE l_stmt USING p_from_date,
227 p_to_date;
228
229 IF(p_currency_code <> l_base_curr) THEN
230 l_stmt := 'insert into ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' (index_date, index_value) ' || '(select conversion_date, round(conversion_rate, 5) from
231 gl_daily_ratesDBLINK
232 where from_currency = ' || '''' || p_base_currency_code || '''' || ' and to_currency = ' || '''' || p_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
233 ' and conversion_type = ''' || fnd_profile.value('MSD_DEM_CONVERSION_TYPE') || ''' )';
234 l_stmt := REPLACE(l_stmt, 'DBLINK', g_dblink);
235 ELSE
236 l_stmt := 'insert into ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' (index_date, index_value) ' || '(select conversion_date, round((1/conversion_rate), 5) from
237 gl_daily_ratesDBLINK
238 where from_currency = ' || '''' || p_currency_code || '''' || ' and to_currency = ' || '''' || p_base_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
239 ' and conversion_type = ''' || fnd_profile.value('MSD_DEM_CONVERSION_TYPE') || ''')';
240 l_stmt := REPLACE(l_stmt, 'DBLINK', g_dblink);
241 END IF;
242
243 msd_dem_common_utilities.log_debug(l_stmt);
244
245 EXECUTE IMMEDIATE l_stmt USING p_from_date,
246 p_to_date;
247 COMMIT;
248
249 l_stmt := 'select count(*) from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where rownum < 5';
250
251 execute immediate l_stmt into l_cur_count;
252
253 if l_cur_count = 0 then
254 msd_dem_common_utilities.log_message('No records exist for currency ' || p_currency_code || ' between ' || p_from_date || ' and ' || p_to_date);
255 end if;
256
257 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
258 msd_dem_common_utilities.log_message('Currency : ' || p_currency_code || ' collection is finished.');
259 msd_dem_common_utilities.log_debug('Currency : ' || p_currency_code || ' collection is finished.');
260
261 END;
262
263 PROCEDURE collect_currency(errbuf OUT NOCOPY VARCHAR2,
264 retcode OUT NOCOPY VARCHAR2,
265 p_instance_id IN NUMBER,
266 p_from_date IN VARCHAR2 DEFAULT NULL,
267 p_to_date IN VARCHAR2 DEFAULT NULL,
268 p_all_currencies IN NUMBER,
269 p_include_currency_list IN VARCHAR2 DEFAULT NULL,
270 p_exclude_currency_list IN VARCHAR2 DEFAULT NULL)
271
272 AS
273
274 /*** LOCAL VARIABLES ****/ x_errbuf VARCHAR2(200) := NULL;
275 x_retcode VARCHAR2(100) := NULL;
276 g_dblink VARCHAR2(30) := NULL;
277
278 l_min_date DATE;
279 l_max_date DATE;
280
281 l_date_to DATE;
282 l_date_from DATE;
283
284 type get_curr_code IS ref CURSOR;
285 c_get_curr_code get_curr_code;
286
287 l_stmt VARCHAR2(3000);
288
289 l_base_curr VARCHAR2(30);
290
291 l_list VARCHAR2(5000);
292 l_list2 VARCHAR2(5000);
293
294 l_curr_code VARCHAR2(30);
295
296 BEGIN
297
298 msd_dem_common_utilities.log_debug('Entering: msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
299 msd_dem_common_utilities.log_message('Entering: msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
300
301 /* Get the db link to the source instance */ msd_dem_common_utilities.get_dblink(x_errbuf, x_retcode, p_instance_id, g_dblink);
302
303 IF(x_retcode = '-1') THEN
304 retcode := -1;
305 errbuf := x_errbuf;
306 msd_dem_common_utilities.log_message('Error(1): msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
307 RETURN;
308 END IF;
309
310 /* Get the min and max date in demantra time */
311
312 get_min_max_date(l_min_date, l_max_date);
313
314 l_base_curr := fnd_profile.VALUE('MSD_DEM_CURRENCY_CODE');
315
316 l_date_from := nvl(fnd_date.canonical_to_date(p_from_date), l_min_date);
317 l_date_to := nvl(fnd_date.canonical_to_date(p_to_date), l_max_date);
318
319 /* Error if p_from_date is greater than p_to_date */
320
321 IF(l_date_from > l_date_to) THEN
322 retcode := -1;
323 errbuf := 'From Date should not be greater than To Date.';
324 msd_dem_common_utilities.log_message('Error: msd_dem_collect_currency.collect_currency- ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
325 msd_dem_common_utilities.log_message(errbuf);
326 RETURN;
327 END IF;
328
329 IF(p_all_currencies = 2) THEN
330
331 IF p_include_currency_list IS NOT NULL
332 AND p_exclude_currency_list IS NOT NULL THEN
333
334 msd_dem_common_utilities.log_message('Both include list and exclude list are specified for currency collection. Please specify either include list or excluse list.');
335 msd_dem_common_utilities.log_debug('Both include list and exclude list are specified for currency collection. Please specify either include list or excluse list.');
336 retcode := -1;
337 RETURN;
338 ELSIF p_include_currency_list IS NULL
339 AND p_exclude_currency_list IS NULL THEN
340
341 msd_dem_common_utilities.log_message('None of include list and exclude list are not specified for currency collection. Please specify either include list or excluse list.');
342 msd_dem_common_utilities.log_debug('None of include list and exclude list are not specified for currency collection. Please specify either include list or excluse list.');
343 retcode := -1;
344 RETURN;
345
346 END IF;
347
348 END IF;
349
350 /* FOR THOSE CURRENCIES WHICH HAVE CONVERSION RATES DEFINED FROM THEM TO THE BASE CURRENCIES. */
351
352 l_stmt := NULL;
356
353
354 l_stmt := 'select distinct from_currency from_currency ' || ' from gl_daily_rates' || g_dblink || ' where to_currency = ''' || l_base_curr || '''' ||
355 ' and conversion_type = ''' || fnd_profile.value('MSD_DEM_CONVERSION_TYPE') || '''' ;
357 l_list := NULL;
358 l_list2 := NULL;
359
360 IF p_include_currency_list IS NOT NULL THEN
361
362 l_list := replace(p_include_currency_list, '''', '''''');
363 l_list := '''' || REPLACE(l_list, ',', ''',''') || '''';
364 l_list2 := p_include_currency_list;
365
366 ELSIF p_exclude_currency_list IS NOT NULL THEN
367
368 l_list := replace(p_exclude_currency_list, '''', '''''');
369 l_list := '''' || REPLACE(l_list, ',', ''',''') || '''';
370
371 END IF;
372
373 l_list2 := l_list2 || ',';
374
375 IF l_list IS NOT NULL THEN
376
377 l_stmt := 'select from_currency from (' || l_stmt;
378 l_stmt := l_stmt || ')' || 'where from_currency ';
379
380 IF p_include_currency_list IS NULL THEN
381 l_stmt := l_stmt || ' not ';
382 END IF;
383
384 l_stmt := l_stmt || ' in ' || '(' || l_list || ')';
385
386 END IF;
387
388 msd_dem_common_utilities.log_debug(l_stmt);
389
390 OPEN c_get_curr_code FOR l_stmt;
391
392 LOOP
393 FETCH c_get_curr_code
394 INTO l_curr_code;
395
396 EXIT
397 WHEN c_get_curr_code % NOTFOUND;
398
399 /*if l_curr_code = l_base_curr then
400 msd_dem_common_utilities.log_message('Warning: Base Currency is selected for collection');
401 retcode := 1;
402 goto continue;
403 end if;*/
404
405 process_currency(x_retcode, l_curr_code, l_base_curr, l_date_from, l_date_to, l_curr_code, g_dblink);
406 l_list2 := REPLACE(l_list2, l_curr_code || ',', '');
407
408 <<continue>>
409 null;
410
411 IF(x_retcode = 1) THEN
412 retcode := 1;
413 errbuf := x_errbuf;
414 END IF;
415
416 END LOOP;
417
418 CLOSE c_get_curr_code;
419
420 if instr(l_list2, l_base_curr) > 0 then
421 msd_dem_common_utilities.log_message('Warning: Base Currency ' || l_base_curr || ' is selected. Base Currency cannot be collected.');
422 l_list2 := RTRIM(l_list2, l_base_curr || ',');
423 retcode := 1;
424 end if;
425
426 l_list2 := RTRIM(l_list2, ',');
427
428 IF(l_list2 IS NOT NULL) THEN
429 retcode := 1;
430 msd_dem_common_utilities.log_message('Warning: Currencies: ' || l_list2 || ' do not exist.');
431 END IF;
432
433 if retcode <> -1 and retcode <> 1 then
434 retcode := 0;
435 end if;
436 RETURN;
437
438 EXCEPTION
439 WHEN others THEN
440 errbuf := SUBSTR(sqlerrm, 1, 150);
441 msd_dem_common_utilities.log_message(errbuf);
442 msd_dem_common_utilities.log_debug(errbuf);
443 retcode := -1;
444
445 END;
446
447 END msd_dem_collect_currency;
448