[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_CURRENCY
Source
1 PACKAGE BODY msd_dem_collect_currency AS
2 /* $Header: msddemccb.pls 120.5.12010000.5 2009/06/08 10:39:07 syenamar ship $ */
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 -- Bug#7199587 syenamar
114 -- Use 'real_table' field to look for empty dummy currencies, 'real_value' field might contain value in any supported language other than english
115
116 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') ||
117 ' where real_value_id in ' ||
118 ' (select distinct real_value_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') ||
119 ' minus ' ||
120 ' select distinct real_value_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ') ' ||
121 ' and real_table in ' ||
122 ' (select real_table from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || ' where real_table like ''EBSCURRENCY%''' ||
123 ' minus ' ||
124 ' select table_name from msd_dem_entities_inuse where ebs_entity = ''Currency'')' ||
125 ' and rownum < 2';
126 msd_dem_common_utilities.log_debug(l_stmt_new_currency_real_value);
127 -- syenamar
128
129 OPEN get_new_currency_real_value FOR l_stmt_new_currency_real_value;
130 FETCH get_new_currency_real_value
131 INTO new_currency;
132
133 IF get_new_currency_real_value % NOTFOUND THEN
134 msd_dem_common_utilities.log_message('Cannot create new currency' || ' as no more empty dummy currency exists.');
135 msd_dem_common_utilities.log_debug('Cannot create new currency' || ' as no more empty dummy currency exists.');
136 CLOSE get_new_currency_real_value;
137 retcode := 1;
138 RETURN;
139 END IF;
140
141 CLOSE get_new_currency_real_value;
142
143 l_stmt := 'insert into msd_dem_entities_inuse(
144 ebs_entity
145 ,demantra_entity
146 ,internal_name
147 ,table_name
148 ,column_name
149 ,last_update_date
150 ,last_updated_by
151 ,creation_date
152 ,created_by
153 ,last_update_login
154 ) values
155 (
156 ''Currency''
157 ,''INDEX''
158 ,:1
159 ,:2
160 ,NULL
161 ,:4
162 ,:5
163 ,:6
164 ,:7
165 ,:8
166 )';
167
168 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;
169 l_stmt := 'update ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'REAL_VALUES') || '
170 set real_value = :1
171 where real_value_id = :2';
172
173 EXECUTE IMMEDIATE l_stmt USING p_currency_code,
174 new_currency.real_value_id;
175
176 -- Bug#7199587 syenamar
177 -- Use component id obtained from lookup
178
179 /*l_stmt_get_component := 'select dcm_product_id from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = '''
180 || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
181
182 OPEN get_component FOR l_stmt_get_component;
183 FETCH get_component
184 INTO l_component_id;
185 CLOSE get_component;*/
186
187 /* Bug#8224935 - APP ID */ -- nallkuma
188 l_component_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
189 'COMP_DM',
190 1,
191 'dcm_product_id'));
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,
201 new_currency.real_value_id;
202
203
204
205 /*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 = '''
206 || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
207
208 OPEN get_component_sop FOR l_stmt_get_component_sop;
209 FETCH get_component_sop
210 INTO l_component_id_sop;
211 CLOSE get_component_sop;*/
212
213 /* Bug#8224935 - APP ID */ -- nallkuma
214 l_component_id_sop := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
215 'COMP_SOP',
216 1,
217 'dcm_product_id'));
218
219 l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || '
220 (dcm_product_id
221 ,real_value_id
222 )
223 (
224 select :1, :2 from dual
225 )';
226 EXECUTE IMMEDIATE l_stmt USING l_component_id_sop, new_currency.real_value_id;
227 -- syenamar
228
229 l_stmt := 'insert into ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
230 ' (display_units_id, real_value_id) ' ||
231 ' (select display_units_id, :1 from ' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' du, msd_dem_entities_inuse mdei ' ||
232 ' where mdei.ebs_entity = ''PRL'' and mdei.internal_name = du.display_units)';
233
234 msd_dem_common_utilities.log_debug(l_stmt);
235
236 execute immediate l_stmt using new_currency.real_value_id;
237
238 END IF;
239
240 msd_dem_common_utilities.log_message('Collecting Currency : ' || p_currency_code);
241 msd_dem_common_utilities.log_debug('Collecting Currency : ' || p_currency_code);
242
243 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
244
245 l_stmt := 'delete from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where index_date between :1 and :2 ';
246
247 msd_dem_common_utilities.log_debug('Bind Variables: ');
248 msd_dem_common_utilities.log_debug('From Date: ' || p_from_date);
249 msd_dem_common_utilities.log_debug('To Date: ' || p_to_date);
250 msd_dem_common_utilities.log_debug('Delete statement for currency: ' || l_stmt);
251
252 EXECUTE IMMEDIATE l_stmt USING p_from_date,
253 p_to_date;
254
255 IF(p_currency_code <> l_base_curr) THEN
256 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
257 gl_daily_ratesDBLINK
258 where from_currency = ' || '''' || p_base_currency_code || '''' || ' and to_currency = ' || '''' || p_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
259 ' and conversion_type = fnd_profile.valueDBLINK(''MSD_DEM_CONVERSION_TYPE'') )';
260 l_stmt := REPLACE(l_stmt, 'DBLINK', g_dblink);
261 ELSE
262 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
263 gl_daily_ratesDBLINK
264 where from_currency = ' || '''' || p_currency_code || '''' || ' and to_currency = ' || '''' || p_base_currency_code || '''' || ' and conversion_date between :1 and :2 ' ||
265 ' and conversion_type = fnd_profile.valueDBLINK(''MSD_DEM_CONVERSION_TYPE'') )';
266 l_stmt := REPLACE(l_stmt, 'DBLINK', g_dblink);
267 END IF;
268
272 p_to_date;
269 msd_dem_common_utilities.log_debug(l_stmt);
270
271 EXECUTE IMMEDIATE l_stmt USING p_from_date,
273 COMMIT;
274
275 l_stmt := 'select count(*) from ' || fnd_profile.VALUE('MSD_DEM_SCHEMA') || '.' || new_currency.real_table || ' where rownum < 5';
276
277 execute immediate l_stmt into l_cur_count;
278
279 if l_cur_count = 0 then
280 msd_dem_common_utilities.log_message('No records exist for currency ' || p_currency_code || ' between ' || p_from_date || ' and ' || p_to_date);
281 end if;
282
283 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
284 msd_dem_common_utilities.log_message('Currency : ' || p_currency_code || ' collection is finished.');
285 msd_dem_common_utilities.log_debug('Currency : ' || p_currency_code || ' collection is finished.');
286
287 END;
288
289 PROCEDURE collect_currency(errbuf OUT NOCOPY VARCHAR2,
290 retcode OUT NOCOPY VARCHAR2,
291 p_instance_id IN NUMBER,
292 p_from_date IN VARCHAR2 DEFAULT NULL,
293 p_to_date IN VARCHAR2 DEFAULT NULL,
294 p_all_currencies IN NUMBER,
295 p_include_currency_list IN VARCHAR2 DEFAULT NULL,
296 p_exclude_currency_list IN VARCHAR2 DEFAULT NULL)
297
298 AS
299
300 /*** LOCAL VARIABLES ****/ x_errbuf VARCHAR2(200) := NULL;
301 x_retcode VARCHAR2(100) := NULL;
302 g_dblink VARCHAR2(30) := NULL;
303
304 l_min_date DATE;
305 l_max_date DATE;
306
307 l_date_to DATE;
308 l_date_from DATE;
309
310 type get_curr_code IS ref CURSOR;
311 c_get_curr_code get_curr_code;
312
313 l_stmt VARCHAR2(3000);
314
315 l_base_curr VARCHAR2(30);
316
317 l_list VARCHAR2(5000);
318 l_list2 VARCHAR2(5000);
319
320 l_curr_code VARCHAR2(30);
321
322 BEGIN
323
324 msd_dem_common_utilities.log_debug('Entering: msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
325 msd_dem_common_utilities.log_message('Entering: msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
326
327 /* Get the db link to the source instance */ msd_dem_common_utilities.get_dblink(x_errbuf, x_retcode, p_instance_id, g_dblink);
328
329 IF(x_retcode = '-1') THEN
330 retcode := -1;
331 errbuf := x_errbuf;
332 msd_dem_common_utilities.log_message('Error(1): msd_dem_collect_currency.collect_currency - ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
333 RETURN;
334 END IF;
335
336 /* Get the min and max date in demantra time */
337
338 get_min_max_date(l_min_date, l_max_date);
339
340 l_base_curr := fnd_profile.VALUE('MSD_DEM_CURRENCY_CODE');
341
342 l_date_from := nvl(fnd_date.canonical_to_date(p_from_date), l_min_date);
343 l_date_to := nvl(fnd_date.canonical_to_date(p_to_date), l_max_date);
344
345 /* Error if p_from_date is greater than p_to_date */
346
347 IF(l_date_from > l_date_to) THEN
348 retcode := -1;
349 errbuf := 'From Date should not be greater than To Date.';
350 msd_dem_common_utilities.log_message('Error: msd_dem_collect_currency.collect_currency- ' || to_char(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
351 msd_dem_common_utilities.log_message(errbuf);
352 RETURN;
353 END IF;
354
355 IF(p_all_currencies = 2) THEN
356
357 IF p_include_currency_list IS NOT NULL
358 AND p_exclude_currency_list IS NOT NULL THEN
359
360 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.');
361 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.');
362 retcode := -1;
363 RETURN;
364 ELSIF p_include_currency_list IS NULL
365 AND p_exclude_currency_list IS NULL THEN
366
367 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.');
368 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.');
369 retcode := -1;
370 RETURN;
371
372 END IF;
373
374 END IF;
375
376 /* FOR THOSE CURRENCIES WHICH HAVE CONVERSION RATES DEFINED FROM THEM TO THE BASE CURRENCIES. */
377
378 l_stmt := NULL;
379
380 l_stmt := 'select distinct from_currency from_currency ' || ' from gl_daily_rates' || g_dblink || ' where to_currency = ''' || l_base_curr || '''' ||
381 ' and conversion_type = fnd_profile.value' || g_dblink || '(''MSD_DEM_CONVERSION_TYPE'') ';
382
383 l_list := NULL;
384 l_list2 := NULL;
385
386 IF p_include_currency_list IS NOT NULL THEN
387
388 l_list := replace(p_include_currency_list, '''', '''''');
389 l_list := '''' || REPLACE(l_list, ',', ''',''') || '''';
390 l_list2 := p_include_currency_list;
391
392 ELSIF p_exclude_currency_list IS NOT NULL THEN
393
394 l_list := replace(p_exclude_currency_list, '''', '''''');
395 l_list := '''' || REPLACE(l_list, ',', ''',''') || '''';
396
397 END IF;
398
399 l_list2 := l_list2 || ',';
400
401 IF l_list IS NOT NULL THEN
402
403 l_stmt := 'select from_currency from (' || l_stmt;
404 l_stmt := l_stmt || ')' || 'where from_currency ';
405
406 IF p_include_currency_list IS NULL THEN
407 l_stmt := l_stmt || ' not ';
408 END IF;
409
410 l_stmt := l_stmt || ' in ' || '(' || l_list || ')';
411
412 END IF;
413
414 msd_dem_common_utilities.log_debug(l_stmt);
415
416 OPEN c_get_curr_code FOR l_stmt;
417
418 LOOP
419 FETCH c_get_curr_code
420 INTO l_curr_code;
421
422 EXIT
423 WHEN c_get_curr_code % NOTFOUND;
424
425 /*if l_curr_code = l_base_curr then
426 msd_dem_common_utilities.log_message('Warning: Base Currency is selected for collection');
427 retcode := 1;
428 goto continue;
429 end if;*/
430
431 process_currency(x_retcode, l_curr_code, l_base_curr, l_date_from, l_date_to, l_curr_code, g_dblink);
432 l_list2 := REPLACE(l_list2, l_curr_code || ',', '');
433
434 <<continue>>
435 null;
436
437 IF(x_retcode = 1) THEN
438 retcode := 1;
439 errbuf := x_errbuf;
440 END IF;
441
442 END LOOP;
443
444 CLOSE c_get_curr_code;
445
446 if instr(l_list2, l_base_curr) > 0 then
447 msd_dem_common_utilities.log_message('Warning: Base Currency ' || l_base_curr || ' is selected. Base Currency cannot be collected.');
448 l_list2 := RTRIM(l_list2, l_base_curr || ',');
449 retcode := 1;
450 end if;
451
452 l_list2 := RTRIM(l_list2, ',');
453
454 IF(l_list2 IS NOT NULL) THEN
455 retcode := 1;
456 msd_dem_common_utilities.log_message('Warning: Currencies: ' || l_list2 || ' do not exist.');
457 END IF;
458
459 if retcode <> -1 and retcode <> 1 then
460 retcode := 0;
461 end if;
462 RETURN;
463
464 EXCEPTION
465 WHEN others THEN
466 errbuf := SUBSTR(sqlerrm, 1, 150);
467 msd_dem_common_utilities.log_message(errbuf);
468 msd_dem_common_utilities.log_debug(errbuf);
469 retcode := -1;
470
471 END;
472
473 END msd_dem_collect_currency;
474