[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_PRICE_LISTS
Source
1 PACKAGE BODY MSD_DEM_COLLECT_PRICE_LISTS AS
2 /* $Header: msddemprlclb.pls 120.14.12020000.2 2012/09/14 16:24:28 nallkuma ship $ */
3
4 v_list varchar2(5000);
5
6 function get_lookup_value(p_lookup_type IN VARCHAR2,
7 p_lookup_code IN VARCHAR2)
8 return VARCHAR2
9
10 as
11
12 cursor get_lookup_value is
13 select meaning
14 from fnd_lookup_values
15 where lookup_type = p_lookup_type
16 and lookup_code = p_lookup_code
17 and language = 'US';
18
19 cursor get_schema_name is
20 select fnd_profile.value('MSD_DEM_SCHEMA')
21 from dual;
22
23 l_lookup_value varchar2(200);
24 l_schema_name varchar2(200);
25
26 begin
27
28 open get_lookup_value;
29 fetch get_lookup_value into l_lookup_value;
30 close get_lookup_value;
31
32 if p_lookup_type = 'MSD_DEM_TABLES' then
33
34 open get_schema_name;
35 fetch get_schema_name into l_schema_name;
36 close get_schema_name;
37
38 if l_schema_name is not null then
39 l_lookup_value := l_schema_name || '.' || l_lookup_value;
40 end if;
41
42 end if;
43
44 return l_lookup_value;
45
46 end;
47
48 procedure populate_prl(retcode out nocopy number
49 ,p_prl_code in varchar2
50 ,p_instance_id number
51 ,p_start_date date
52 ,p_end_date date)
53
54 as
55
56 cursor get_prl_metadata is
57 select table_name, column_name
58 from msd_dem_entities_inuse
59 where ebs_entity = 'PRL'
60 and demantra_entity = 'DISPLAY_UNIT'
61 and internal_name = p_prl_code;
62
63 type c_get_series_id is ref cursor;
64 get_series_id c_get_series_id;
65 l_stmt_get_series_id varchar2(2000);
66
67 l_stmt varchar2(7000);
68
69 l_series_id number;
70
71 l_table_name varchar2(100);
72 l_column_name varchar2(100);
73
74 begin
75
76 open get_prl_metadata;
77 fetch get_prl_metadata into l_table_name, l_column_name;
78
79 if get_prl_metadata%notfound then
80 close get_prl_metadata;
81 msd_dem_common_utilities.log_message('Price List deleted. Please recreate the Price List');
82 msd_dem_common_utilities.log_debug('Price List deleted. Please recreate the Price List');
83 retcode := 1;
84 return;
85 end if;
86
87 close get_prl_metadata;
88
89 msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PRICE_LIST_INTO_DEMANTRA', p_instance_id);
90
91
92 if fnd_profile.value('MSD_DEM_SCHEMA') is not null then
93 l_stmt := replace(l_stmt, 'TABLENAME', fnd_profile.value('MSD_DEM_SCHEMA') || '.' || l_table_name);
94 l_stmt := replace(l_stmt, 'SCHEMANAME', fnd_profile.value('MSD_DEM_SCHEMA'));
95 else
96 l_stmt := replace(l_stmt, 'TABLENAME', l_table_name);
97 end if;
98
99 l_stmt := replace(l_stmt, 'COLUMNNAME', l_column_name);
100
101
102 msd_dem_common_utilities.log_debug('Bind variables: ');
103 msd_dem_common_utilities.log_debug('p_instance_id: ' || p_instance_id);
104 msd_dem_common_utilities.log_debug('p_start_date: ' || p_start_date);
105 msd_dem_common_utilities.log_debug('p_end_date: ' || p_end_date);
106 msd_dem_common_utilities.log_debug('p_prl_code: ' || p_prl_code);
107
108 msd_dem_common_utilities.log_debug('Executed Statement: ');
109 msd_dem_common_utilities.log_debug(l_stmt);
110
111 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') );
112 execute immediate l_stmt using p_start_date, p_end_date, p_prl_code ;
113 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
114
115 l_stmt_get_series_id := 'select forecast_type_id from ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS') ||
116 ' where computed_name = ''' || l_column_name || '''';
117
118 open get_series_id for l_stmt_get_series_id;
119 fetch get_series_id into l_series_id;
120 if get_series_id%NOTFOUND then
121 retcode := 1;
122 goto continue;
123 end if;
124 close get_series_id;
125
126 -- Bug#7199587 syenamar
127 -- use integration interface id (for 'EBS Price List' integration interface) obtained from lookup
128 /* Bug# 8224935 - APP ID */
129 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES') || ' tqs set tqs.load_option = 0, tqs.purge_option = 0 '
130 || ' where tqs.id = (select tq.id from '
131 || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
132 || ' where tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id')
133 || ') and tqs.series_id = ' || l_series_id;
134
135 msd_dem_common_utilities.log_debug(l_stmt);
136
137 execute immediate l_stmt;
138 -- syenamar
139
140 <<continue>>
141
142 null;
143
144 exception
145 when others then
146 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
147 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
148 retcode := 1;
149
150 end;
151
152 procedure process_prl(retcode out nocopy number, p_prl_code in varchar2)
153
154 as
155
156 cursor verify_entities_inuse is
157 select 1 from
158 msd_dem_entities_inuse
159 where internal_name = p_prl_code
160 and ebs_entity = 'PRL';
161
162 type c_get_new_prl_display_unit is ref cursor;
163 get_new_prl_display_unit c_get_new_prl_display_unit;
164 l_stmt_new_prl_display_unit varchar2(2000);
165
166 type c_get_component is ref cursor;
167 get_component c_get_component;
168 l_stmt_get_component varchar2(2000);
169
170 type c_get_component_sop is ref cursor;
171 get_component_sop c_get_component_sop;
172 l_stmt_get_component_sop varchar2(2000);
173
174 type c_get_profile_table is ref cursor;
175 get_profile_table c_get_profile_table;
176 l_stmt_get_profile_table varchar2(2000);
177
178 type c_get_seeded_unit is ref cursor;
179 get_seeded_unit c_get_seeded_unit;
180 l_stmt_get_seeded_unit varchar2(2000);
181
182 l_get_seeded_unit varchar2(250);
183
184 l_profile_table_name varchar2(1000);
185
186 l_verify_entities_inuse number;
187
188 type new_prl_rectype is record(
189 display_units varchar2(500)
190 ,display_units_id number
191 ,data_table varchar2(500)
192 ,data_field varchar2(500)
193 );
194
195 new_prl new_prl_rectype;
196
197 l_component_id number;
198 l_component_id_sop number;
199
200 l_stmt varchar2(2000);
201
202 begin
203
204 l_stmt_get_seeded_unit := 'select display_units from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
205 ' where display_units = ''' || p_prl_code || '''';
206
207 open get_seeded_unit for l_stmt_get_seeded_unit;
208 fetch get_seeded_unit into l_get_seeded_unit;
209 close get_seeded_unit;
210
211 open verify_entities_inuse;
212 fetch verify_entities_inuse into l_verify_entities_inuse;
213 close verify_entities_inuse;
214
215 if l_verify_entities_inuse is null then
216
217 if l_get_seeded_unit is not null then
218 msd_dem_common_utilities.log_message('Seeded Display Unit with name ' || p_prl_code || ' exist in Demantra. This Price List will not created');
219 msd_dem_common_utilities.log_debug('Seeded Display Unit with name ' || p_prl_code || ' exist in Demantra. This Price List will not created');
220 retcode := 1;
221 return;
222 end if;
223
224 -- Bug#7199587 syenamar
225 -- Use 'data_field' field to look for empty dummy pricelists, 'display_units' field may contain values in any supported language other than english
226 l_stmt_new_prl_display_unit := 'select display_units ,display_units_id ,data_table ,data_field ' ||
227 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
228 ' where display_units_id in ' ||
229 ' (select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
230 ' minus ' ||
231 ' select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') || ')' ||
232 ' and data_field in ' ||
233 ' (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where data_field like ''EBSPRICELIST%'' ' ||
234 ' minus ' ||
235 ' select column_name from msd_dem_entities_inuse where ebs_entity = ''PRL'' )' ||
236 ' and rownum < 2';
237
238 msd_dem_common_utilities.log_debug(l_stmt_new_prl_display_unit);
239 -- syenamar
240
241 open get_new_prl_display_unit for l_stmt_new_prl_display_unit;
242 fetch get_new_prl_display_unit into new_prl;
243 if get_new_prl_display_unit%notfound then
244 msd_dem_common_utilities.log_message('Seeded Display Units for Price List not Available');
245 msd_dem_common_utilities.log_debug('Seeded Display Units for Price List not Available');
246 close get_new_prl_display_unit;
247 retcode := 1;
248 return;
249 end if;
250 close get_new_prl_display_unit;
251
252
253 /* Bug# 8224935 - APP ID */
254 l_stmt_get_profile_table := 'select tq.table_name from '
255 --|| get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST') || ' tl ' || ' ,'
256 || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
257 || ' where tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id');
258
259
260 open get_profile_table for l_stmt_get_profile_table;
261 fetch get_profile_table into l_profile_table_name;
262 close get_profile_table;
263
264
265
266 l_stmt := 'insert into msd_dem_entities_inuse(
267 ebs_entity
268 ,demantra_entity
269 ,internal_name
270 ,table_name
271 ,column_name
272 ,last_update_date
273 ,last_updated_by
274 ,creation_date
275 ,created_by
276 ,last_update_login
277 ) values
278 (
279 ''PRL''
280 ,''DISPLAY_UNIT''
281 ,:1
282 ,:2
283 ,:3
284 ,:4
285 ,:5
286 ,:6
287 ,:7
288 ,:8
289 )';
290
291 msd_dem_common_utilities.log_debug(l_stmt);
292
293
294 execute immediate l_stmt using p_prl_code, l_profile_table_name, new_prl.data_field, sysdate, nvl(fnd_global.user_id,-1), sysdate, nvl(fnd_global.user_id,-1), fnd_global.user_id;
295
296 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
297 ' set display_units = :1 ' ||
298 ' where display_units_id = :2';
299
300 msd_dem_common_utilities.log_debug(l_stmt);
301
302 execute immediate l_stmt using p_prl_code, new_prl.display_units_id;
303
304 -- syenamar
305 -- bug#8323116: avoid inserting records already present in avail_units else collection will fail
306 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
307 ' (group_table_id ,display_units_id )' ||
308 ' (select group_table_id, :1 ' ||
309 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'GROUP_TABLES') ||
310 ' where group_type = 1 ' ||
311 ' minus ' ||
312 ' select group_table_id, display_units_id ' ||
313 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
314 ' where display_units_id = :2 ' ||
315 ' )';
316
317 msd_dem_common_utilities.log_debug(l_stmt);
318
319 execute immediate l_stmt using new_prl.display_units_id, new_prl.display_units_id;
320 -- syenamar
321
322 /*l_stmt_get_component := 'select dcm_product_id from ' ||get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'DEMAND_MANAGEMENT') || '''';
323
324 msd_dem_common_utilities.log_debug(l_stmt_get_component);
325
326 open get_component for l_stmt_get_component;
327 fetch get_component into l_component_id;
328 close get_component;*/
329
330 /* Bug# 8224935 - APP ID */
331 l_component_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_DM', 1, 'dcm_product_id'));
332 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
333 ' (dcm_product_id ,display_units_id) ' ||
334 ' (select :1, :2 from dual)';
335
336 msd_dem_common_utilities.log_debug(l_stmt);
337 execute immediate l_stmt using l_component_id,new_prl.display_units_id;
338
339 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
340 ' (display_units_id, real_value_id) ' ||
341 ' (select :1, real_value_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ' dpi ' ||
342 ' where dpi.dcm_product_id = ' || l_component_id ||
343 ')';
344
345 msd_dem_common_utilities.log_debug(l_stmt);
346
347 execute immediate l_stmt using new_prl.display_units_id;
348
349
350
351 /*l_stmt_get_component_sop := 'select dcm_product_id from ' ||get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS') || ' where product_name = ''' || msd_dem_common_utilities.get_lookup_value('MSD_DEM_COMPONENTS', 'SOP') || '''';
352
353 msd_dem_common_utilities.log_debug(l_stmt_get_component_sop);
354
355 open get_component_sop for l_stmt_get_component_sop;
356 fetch get_component_sop into l_component_id_sop;
357 close get_component_sop;*/
358
359 /* Bug# 8224935 - APP ID */
360 l_component_id_sop := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_SOP', 1, 'dcm_product_id'));
361 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
362 ' (dcm_product_id ,display_units_id) ' ||
363 ' (select :1, :2 from dual)';
364
365 msd_dem_common_utilities.log_debug(l_stmt);
366 execute immediate l_stmt using l_component_id_sop,new_prl.display_units_id;
367 --syenamar
368
369 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS')
370 || ' set computed_title = :1 '
371 || ' where computed_name = :2 ';
372
373 execute immediate l_stmt using substr(p_prl_code, 1 , 50), new_prl.data_field;
374
375
376 /*else
377 l_stmt := null;
378 l_stmt := 'update msd_dem_entities_inuse set table_name = ''biio_ebs_price_list''' ||
379 ', column_name = (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :1)' ||
380 'where ebs_entity = ''PRL'' and demantra_entity = ''DISPLAY_UNIT'' and internal_name = :2';
381
382 msd_dem_common_utilities.log_debug(l_stmt);
383
384 execute immediate l_stmt using p_prl_code, p_prl_code; */
385
386 end if;
387
388
389
390 exception
391 when others then
392 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
393 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
394 retcode := 1;
395
396 end;
397
398 procedure populate_demantra_prl_table(errbuf out nocopy varchar2,
399 retcode out nocopy number,
400 p_instance_id in number,
401 p_include_prl_list in varchar2,
402 p_exclude_prl_list in varchar2,
403 p_start_date in date,
404 p_end_date in date)
405
406 as
407
408 type c_prl_code is ref cursor;
409
410 get_prl_code c_prl_code;
411
412 l_stmt varchar2(500);
413 l_list2 varchar2(500);
414
415 l_prl_code varchar2(250);
416
417 was_retcode_1 number := 0;
418
419 begin
420
421 /* Bug# 8224935 - APP ID */
422 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES') || ' tqs set tqs.load_option = 2, tqs.purge_option = 0 '
423 || ' where tqs.id = (select tq.id from '
424 || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
425 || ' where tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id')
426 || ')';
427
428 msd_dem_common_utilities.log_debug(l_stmt);
429
430 execute immediate l_stmt;-- using fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE');
431 -- syenamar
432
433 l_stmt := null;
434 l_stmt := 'select distinct price_list_name price_list_name
435 from msd_dem_price_lists_gtt';
436
437
438 /*l_list2 := null;
439
440 if p_include_prl_list is not null then
441
442 l_list2 := p_include_prl_list;
443
444 end if;
445
446 l_list2 := l_list2 || ',';*/
447
448 l_list2 := v_list;
449 l_list2 := replace(l_list2, '''', '');
450 l_list2 := l_list2 || ',';
451
452 if v_list is not null then
453
454 l_stmt := 'select price_list_name from (' || l_stmt;
455 l_stmt := l_stmt || ')' || 'where price_list_name ';
456
457 if p_exclude_prl_list is not null then
458 l_stmt := l_stmt || ' not ';
459 end if;
460
461 l_stmt := l_stmt || ' in ' || '(' || v_list || ')';
462
463 end if;
464
465
466
467 open get_prl_code for l_stmt;
468
469 loop
470
471 fetch get_prl_code into l_prl_code;
472 exit when get_prl_code%notfound;
473
474 msd_dem_common_utilities.log_message('Populating Price List: ' || l_prl_code);
475 msd_dem_common_utilities.log_debug('Populating Price List: ' || l_prl_code);
476
477 l_list2 := replace(l_list2, l_prl_code||',' , '');
478
479 process_prl(retcode, l_prl_code);
480
481 if retcode = -1 or retcode = 1 then
482 msd_dem_common_utilities.log_message('Failed processing Price List: ' || l_prl_code);
483 msd_dem_common_utilities.log_debug('Failed Processing Price List: ' || l_prl_code);
484 was_retcode_1 := retcode;
485 goto continue;
486 end if;
487
488 populate_prl(retcode, l_prl_code, p_instance_id, p_start_date, p_end_date);
489
490 if retcode = -1 or retcode = 1 then
491 was_retcode_1 := retcode;
492 end if;
493
494 <<continue>>
495 null;
496
497 end loop;
498
499 close get_prl_code;
500
501 retcode := was_retcode_1;
502
503 l_list2 := rtrim(l_list2, ',');
504
505 if l_list2 is not null then
506 retcode := 1;
507 msd_dem_common_utilities.log_message('Following Price Lists dont exist in source: ' || l_list2);
508 msd_dem_common_utilities.log_debug('Following Price Lists dont exist in source: ' || l_list2);
509 end if;
510
511
512 exception
513 when others then
514 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
515 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
516 retcode := 1;
517
518 end;
519
520 procedure filter_from_list(errbuf out nocopy varchar2,
521 retcode out nocopy number,
522 p_instance_id in number,
523 p_include_prl_list in varchar2,
524 p_exclude_prl_list in varchar2)
525 as
526
527 cursor count_price_lists is
528 select count(*)
529 from msd_dem_price_lists;
530
531 l_count_price_lists number;
532
533 cursor price_list_cur is
534 select price_list_name
535 from msd_dem_price_lists;
536
537 acc_list varchar2(5000);
538 unacc_list varchar2(5000);
539
540 begin
541
542 open count_price_lists;
543 fetch count_price_lists into l_count_price_lists;
544 close count_price_lists;
545
546 if l_count_price_lists = 0 then
547 retcode := -1;
548 return;
549 end if;
550
551 unacc_list := v_list || ',';
552 acc_list := null;
553
554 for price_list_cur_rec in price_list_cur loop
555
556 if (instr(v_list, price_list_cur_rec.price_list_name) > 0 and p_include_prl_list is not null) or v_list is null then
557
558 acc_list := acc_list || '''' || price_list_cur_rec.price_list_name || ''',';
559 unacc_list := replace(unacc_list,'''' || price_list_cur_rec.price_list_name || ''',','');
560
561 elsif (instr(v_list, price_list_cur_rec.price_list_name) = 0 and p_exclude_prl_list is not null) then
562 acc_list := acc_list || '''' || price_list_cur_rec.price_list_name || ''',';
563 unacc_list := replace(unacc_list,'''' || price_list_cur_rec.price_list_name || ''',','');
564
565 end if;
566 end loop;
567
568 if acc_list is not null then
569 acc_list := rtrim(acc_list, ',');
570 end if;
571
572 unacc_list := rtrim(unacc_list, ',');
573 unacc_list := replace(unacc_list, '''', '');
574
575 if unacc_list is not null then
576 msd_dem_common_utilities.log_message('The following price list names are not collected as they dont exist in the price list form or excluded by the user: ' || unacc_list);
577 msd_dem_common_utilities.log_debug('The following price list names are not collected as they dont exist in the price list form or excluded by the user: ' || unacc_list);
578
579 if acc_list is not null then
580 retcode := 1;
581 else
582 retcode := 0 ;
583 return;
584 end if;
585 end if;
586
587 v_list := acc_list;
588
589 if retcode <> -1 and retcode <> 1 then
590 retcode := 0;
591 end if;
592 msd_dem_common_utilities.log_message('In filter_from_list procedure - retcode: '|| retcode);-- nallkuma bug#13857980
593
594 exception
595 when others then
596 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
597 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
598 retcode := -1;
599
600 end;
601
602 procedure collect_from_source(errbuf out nocopy varchar2,
603 retcode out nocopy number,
604 p_instance_id in number,
605 p_include_prl_list in varchar2,
606 p_exclude_prl_list in varchar2)
607
608 as
609
610 l_add_where_clause varchar2(5000);
611 l_stmt varchar2(6000);
612 l_key_values varchar2(4000);
613
614 l_retcode number;
615
616 begin
617
618 v_list := null;
619 if p_include_prl_list is not null then
620
621 v_list := '''' || replace(p_include_prl_list, ',', ''',''') || '''';
622
623 elsif p_exclude_prl_list is not null then
624
625 v_list := '''' || replace(p_exclude_prl_list, ',', ''',''') || '''';
626
627 end if;
628
629 filter_from_list(errbuf, retcode, p_instance_id, p_include_prl_list, p_exclude_prl_list);
630
631
632 if retcode = -1 then
633 msd_dem_common_utilities.log_message('Price list collection cannot continue as no price lists are selected in the price list form');
634 msd_dem_common_utilities.log_debug('Price list collection cannot continue as no price lists are selected in the price list form');
635 retcode := -1;
636 return;
637 end if;
638
639 if retcode = 0 then
640 msd_dem_common_utilities.log_message('Include Price List does not contain any price list among the price lists, which are selected in the price list form.');
641 msd_dem_common_utilities.log_debug('Include Price List does not contain any price list among the price lists, which are selected in the price list form.');
642 retcode := 0;
643 return;
644 end if;
645
646 l_add_where_clause := null;
647
648 if v_list is not null then
649
650 l_add_where_clause := 'AND qplh.name ';
651
652 --if p_exclude_prl_list is not null then
653 -- l_add_where_clause := l_add_where_clause || ' not ';
654 --end if;
655
656 l_add_where_clause := l_add_where_clause || ' in ' || '(' || v_list || ') ';
657
658 end if;
659
660 l_stmt := null;
661
662 if l_add_where_clause is null then
663 l_add_where_clause := ' and 1=1 ';
664 end if;
665
666 /*msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PRICE_LIST_FROM_SOURCE', p_instance_id, null, l_add_where_clause ); */
667
668 l_key_values := '$C_INSTANCE#' || p_instance_id ||
669 '$C_ADD_WHERE_CLAUSE#' || l_add_where_clause || '$';
670
671 msd_dem_query_utilities.get_query2 (
672 l_retcode,
673 l_stmt,
674 'MSD_DEM_PRL_FROM_SOURCE',
675 p_instance_id,
676 l_key_values,
677 0,
678 null);
679
680 msd_dem_common_utilities.log_debug('Bind variables: ');
681 msd_dem_common_utilities.log_debug('p_instance_id: ' || p_instance_id);
682 msd_dem_common_utilities.log_debug('p_instance_id: ' || p_instance_id);
683
684 msd_dem_common_utilities.log_debug('Executed Statement: ');
685 msd_dem_common_utilities.log_debug(l_stmt);
686
687 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
688 execute immediate l_stmt;
689 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
690 msd_dem_common_utilities.log_message('In collect_from_source procedure - retcode: '|| retcode);-- nallkuma bug#13857980
691
692 exception
693 when others then
694 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
695 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
696 retcode := -1;
697
698 end;
699
700 procedure collect_price_lists(errbuf out nocopy varchar2,
701 retcode out nocopy number,
702 p_instance_id in number,
703 p_start_date in varchar2,
704 p_end_date in varchar2,
705 p_include_all in number,
706 p_include_prl_list in varchar2,
707 p_exclude_prl_list in varchar2)
708
709 as
710
711 retcode_store number;
712
713 /* Bug# 6459467 - Refresh the price list data profile */
714 x_profile_id NUMBER := NULL;
715 x_stmt VARCHAR2(4000) := NULL;
716
717 /* CTO */
718 x_schema VARCHAR2(100) := NULL;
719 x_price_list_col VARCHAR2(30) := NULL;
720 x_errbuf VARCHAR2(200) := NULL;
721 x_retcode VARCHAR2(100) := NULL;
722 x_num_rows NUMBER := NULL;
723
724 cursor c_get_instance_type is
725 select instance_type
726 from msc_apps_instances
727 where instance_id = p_instance_id;
728
729 l_instance_type number;
730
731 begin
732
733 -- bug#8517693 nallkuma
734 open c_get_instance_type ;
735 fetch c_get_instance_type into l_instance_type ;
736 close c_get_instance_type ;
737
738 -- bug#8367471 nallkuma
739 if l_instance_type in (1,2,4) then
740 msd_dem_push_setup_parameters.push_setup_parameters(ERRBUF, RETCODE, p_instance_id, '-999');
741 if retcode = -1 then
742 msd_dem_common_utilities.log_message('Push Setup Parameters Failed');
743 msd_dem_common_utilities.log_debug('Push Setup Parameters Failed');
744 return;
745 end if;
746 else
747 /* Calling push_legacy_setup_parameters() procedure in case of pure legacy instance */
748 msd_dem_push_setup_parameters.push_legacy_setup_parameters(ERRBUF, RETCODE, p_instance_id);
749 if retcode = -1 then
750 msd_dem_common_utilities.log_message('Push Legacy Setup Parameters Failed');
751 msd_dem_common_utilities.log_debug('Push Legacy Setup Parameters Failed');
752 return;
753 end if;
754 end if;
755
756
757 if p_include_all = 1 and (p_include_prl_list is not null or p_exclude_prl_list is not null) then
758 msd_dem_common_utilities.log_message('Cannot specify both collect all and include or exclude list');
759 msd_dem_common_utilities.log_debug('Cannot specify both collect all and include or exclude list');
760 retcode := -1;
761 return;
762 end if;
763
764 if p_include_all = 2 and p_include_prl_list is null and p_exclude_prl_list is null then
765
766 msd_dem_common_utilities.log_message('Exactly one of the parameters Include Price Lists or Exclude Price Lists must be specified, when Collect All Price Lists is No');
767 msd_dem_common_utilities.log_debug('Exactly one of the parameters Include Price Lists or Exclude Price Lists must be specified, when Collect All Price Lists is No');
768 retcode := -1;
769 return;
770
771 end if;
772
773 if p_include_all = 2 and p_include_prl_list is not null and p_exclude_prl_list is not null then
774
775 msd_dem_common_utilities.log_message('Should not specify both include and exclude list');
776 msd_dem_common_utilities.log_debug('Should not specify both include and exclude list');
777 retcode := -1;
778 return;
779
780 end if;
781
782 if nvl(fnd_date.canonical_to_date(p_start_date), to_date('01-01-1900', 'DD-MM-YYYY')) > nvl(fnd_date.canonical_to_date(p_end_date), to_date('01-01-4000', 'DD-MM-YYYY')) then
783 msd_dem_common_utilities.log_message('From Date should not be greater than To Date');
784 msd_dem_common_utilities.log_debug('From Date should not be greater than To Date');
785 retcode := -1;
786 return;
787 end if;
788
789 msd_dem_common_utilities.log_message('Collecting Price Lists');
790 msd_dem_common_utilities.log_debug('Collecting Price Lists');
791
792 msd_dem_common_utilities.log_message('Collecting Price Lists from source');
793 msd_dem_common_utilities.log_debug('Collecting Price Lists from source');
794 collect_from_source(errbuf, retcode, p_instance_id, p_include_prl_list, p_exclude_prl_list);
795
796 if retcode = -1 then
797 return;
798 end if;
799
800 retcode_store := retcode;
801
802 msd_dem_common_utilities.log_message('Populating Price Lists in Demand Planning components');
803 msd_dem_common_utilities.log_debug('Populating Price Lists in Demand Planning components');
804 populate_demantra_prl_table(errbuf, retcode, p_instance_id, p_include_prl_list, p_exclude_prl_list, nvl(fnd_date.canonical_to_date(p_start_date),
805 to_date('01-01-1900', 'DD-MM-YYYY')), nvl(fnd_date.canonical_to_date(p_end_date), to_date('01-01-4000', 'DD-MM-YYYY')));
806
807 commit;
808
809
810 /* Bug# 6459467 - Refresh the price list data profile */
811 BEGIN
812
813 /* Bug# 8224935 - APP ID */
814 x_stmt:= 'SELECT tq.id FROM '
815 || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
816 || ' WHERE tq.transfer_id = ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'INTG_INF_EBS_PRICE_LIST', 1, 'id');
817
818 msd_dem_common_utilities.log_debug ('Get data profile id stmt : ' || x_stmt);
819 EXECUTE IMMEDIATE x_stmt INTO x_profile_id;
820 -- syenamar
821
822 x_stmt := 'BEGIN '
823 || fnd_profile.value('MSD_DEM_SCHEMA') || '.'
824 || 'API_NOTIFY_APS_INTEGRATION('
825 || x_profile_id
826 ||'); end;';
827
828 msd_dem_common_utilities.log_debug ('Refresh data profile stmt : ' || x_stmt);
829 EXECUTE IMMEDIATE x_stmt;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 msd_dem_common_utilities.log_message('WARNING: Failed to update the price list data profile');
834 END;
835
836 commit;
837
838 msd_dem_common_utilities.log_message('In collect_price_lists procedure - retcode: '|| retcode);-- nallkuma bug#13857980
839
840 if retcode <> -1 and retcode <> 1 then
841 retcode := 0;
842 end if;
843
844 if retcode = 0 then
845 retcode := retcode_store;
846 end if;
847
848 <<final>>
849
850 null;
851
852 IF (nvl(retcode, 0) = -1)
853 THEN
854 RETURN;
855 END IF;
856
857 /* CTO - One of the EBS Price List should also be loaded into CTO Data */
858 IF (fnd_profile.value('MSD_DEM_INCLUDE_DEPENDENT_DEMAND') = 1)
859 THEN
860
861 msd_dem_common_utilities.log_debug('Begin - CTO Price');
862
863 /* Get the price list column that should be used to copy from MSD_DEM_PRICE_LIST */
864
865 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
866 IF (x_schema IS NULL)
867 THEN
868 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
869 || ' Unable to get Demantra schema name ');
870 retcode := -1;
871 RETURN;
872 END IF;
873
874 /* Bug# 8224935 - APP ID */
875 x_stmt := 'SELECT dbname FROM ' || x_schema || '.computed_fields '
876 || ' WHERE ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'SERIES_UNIT_PRICE', 2, null);
877 msd_dem_common_utilities.log_debug (x_stmt);
878
879 EXECUTE IMMEDIATE x_stmt INTO x_price_list_col;
880
881 IF (x_price_list_col IS NULL)
882 THEN
883 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
884 || ' Price List Column name is null ');
885 retcode := -1;
886 RETURN;
887 END IF;
888
889 msd_dem_common_utilities.log_debug('Truncate staging table for cto price - BIIO_CTO_OPTION_PRICE ');
890 msd_dem_query_utilities.truncate_table (
891 x_errbuf,
892 x_retcode,
893 'BIIO_CTO_OPTION_PRICE',
894 1,
895 1);
896 IF (x_retcode = -1)
897 THEN
898 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
899 || ' Failed to truncate BIIO_CTO_OPTION_PRICE');
900 retcode := -1;
901 errbuf := x_errbuf;
902 RETURN;
903 END IF;
904
905 msd_dem_common_utilities.log_debug('Truncate staging table for cto price - BIIO_CTO_OPTION_PRICE_ERR ');
906 msd_dem_query_utilities.truncate_table (
907 x_errbuf,
908 x_retcode,
909 'BIIO_CTO_OPTION_PRICE_ERR',
910 1,
911 1);
912 IF (x_retcode = -1)
913 THEN
914 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
915 || ' Failed to truncate BIIO_CTO_OPTION_PRICE_ERR');
916 retcode := -1;
917 errbuf := x_errbuf;
918 RETURN;
919 END IF;
920
921 /* Build insert statement for biio_cto_option_price */
922 x_stmt := 'INSERT /*+ APPEND NOLOGGING */ INTO ' || x_schema || '.BIIO_CTO_OPTION_PRICE '
923 || ' ( SDATE, LEVEL1, OPTION_PRICE ) '
924 || ' SELECT '
925 || ' SDATE, '
926 || ' LEVEL1, '
927 || x_price_list_col || ' '
928 || ' FROM ' || x_schema || '.MSD_DEM_PRICE_LIST '
929 || ' WHERE ' || x_price_list_col || ' IS NOT NULL ';
930
931 msd_dem_common_utilities.log_debug (x_stmt);
932 msd_dem_common_utilities.log_debug ('Query start time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
933 EXECUTE IMMEDIATE x_stmt;
934 x_num_rows := SQL%ROWCOUNT;
935 COMMIT;
936 msd_dem_common_utilities.log_debug ('Query end time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
937 msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
938
939 msd_dem_common_utilities.log_debug('End - CTO Price');
940
941 ELSE
942 msd_dem_common_utilities.log_debug ('msd_dem_collect_price_lists.collect_price_lists - INFO - '
943 || 'Include Dependent Demand is set to No. Hence no action taken for CTO Price');
944 END IF;
945
946 /* Populate Scenario Price Staging Table if present */
947
948 x_num_rows := NULL;
949 x_price_list_col := NULL;
950 x_schema := fnd_profile.value('MSD_DEM_SCHEMA');
951 IF (x_schema IS NULL)
952 THEN
953 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR(2) - '
954 || ' Unable to get Demantra schema name ');
955 retcode := -1;
956 RETURN;
957 END IF;
958
959 x_stmt := 'SELECT count(1) FROM dba_objects WHERE owner = upper(''' || x_schema || ''') AND object_type = ''TABLE'' '
960 || ' AND object_name IN (''BIIO_SCENARIO_PRICE'', ''BIIO_SCENARIO_PRICE_ERR'') ';
961 msd_dem_common_utilities.log_debug (x_stmt);
962 EXECUTE IMMEDIATE x_stmt INTO x_num_rows;
963
964 IF (x_num_rows = 2)
965 THEN
966
967 msd_dem_common_utilities.log_debug('Begin - Scenario Price');
968
969 /* Get the price list column that should be used to copy from MSD_DEM_PRICE_LIST */
970 x_stmt := 'SELECT dbname FROM ' || x_schema || '.computed_fields '
971 || ' WHERE ' || msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID', 'SERIES_UNIT_PRICE', 2, null);
972 msd_dem_common_utilities.log_debug (x_stmt);
973
974 EXECUTE IMMEDIATE x_stmt INTO x_price_list_col;
975
976 IF (x_price_list_col IS NULL)
977 THEN
978 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
979 || ' Price List Column name is null ');
980 retcode := -1;
981 RETURN;
982 END IF;
983
984 msd_dem_common_utilities.log_debug('Truncate staging table for cto price - BIIO_SCENARIO_PRICE ');
985 msd_dem_query_utilities.truncate_table (
986 x_errbuf,
987 x_retcode,
988 'BIIO_SCENARIO_PRICE',
989 1,
990 1);
991 IF (x_retcode = -1)
992 THEN
993 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
994 || ' Failed to truncate BIIO_SCENARIO_PRICE');
995 retcode := -1;
996 errbuf := x_errbuf;
997 RETURN;
998 END IF;
999
1000 msd_dem_common_utilities.log_debug('Truncate staging table for cto price - BIIO_SCENARIO_PRICE_ERR ');
1001 msd_dem_query_utilities.truncate_table (
1002 x_errbuf,
1003 x_retcode,
1004 'BIIO_SCENARIO_PRICE_ERR',
1005 1,
1006 1);
1007 IF (x_retcode = -1)
1008 THEN
1009 msd_dem_common_utilities.log_message ('msd_dem_collect_price_lists.collect_price_lists - ERROR - '
1010 || ' Failed to truncate BIIO_SCENARIO_PRICE_ERR');
1011 retcode := -1;
1012 errbuf := x_errbuf;
1013 RETURN;
1014 END IF;
1015
1016 /* Build insert statement for biio_scenario_price */
1017 x_stmt := 'INSERT /*+ APPEND NOLOGGING */ INTO ' || x_schema || '.BIIO_SCENARIO_PRICE '
1018 || ' ( SDATE, LEVEL1, SCENARIO_PRICE ) '
1019 || ' SELECT '
1020 || ' SDATE, '
1021 || ' LEVEL1, '
1022 || x_price_list_col || ' '
1023 || ' FROM ' || x_schema || '.MSD_DEM_PRICE_LIST '
1024 || ' WHERE ' || x_price_list_col || ' IS NOT NULL ';
1025
1026 msd_dem_common_utilities.log_debug (x_stmt);
1027 msd_dem_common_utilities.log_debug ('Query start time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1028 EXECUTE IMMEDIATE x_stmt;
1029 x_num_rows := SQL%ROWCOUNT;
1030 COMMIT;
1031 msd_dem_common_utilities.log_debug ('Query end time - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
1032 msd_dem_common_utilities.log_debug ('Number of rows inserted - ' || to_char(x_num_rows));
1033
1034 msd_dem_common_utilities.log_debug('End - Scenario Price');
1035
1036 ELSE
1037 msd_dem_common_utilities.log_debug ('msd_dem_collect_price_lists.collect_price_lists - INFO - '
1038 || 'Staging tables for Scenario Price not found. Hence no action taken for Scenario Price');
1039
1040 END IF;
1041
1042 exception
1043 when others then
1044 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
1045 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
1046 retcode := -1;
1047
1048 end;
1049
1050 procedure delete_price_lists(errbuf out nocopy varchar2,
1051 retcode out nocopy number,
1052 p_list in varchar2)
1053 as
1054
1055 cursor entities_inuse_cur(p_price_list_name varchar2) is
1056 select internal_name, column_name
1057 from msd_dem_entities_inuse
1058 where internal_name = p_price_list_name
1059 and ebs_entity = 'PRL';
1060
1061 l_internal_name varchar2(240);
1062 l_column_name varchar2(240);
1063
1064 l_list varchar2(2000);
1065
1066 type c_get_prl_display_unit_id is ref cursor;
1067 get_prl_display_unit_id c_get_prl_display_unit_id;
1068 l_stmt_prl_display_unit_id varchar2(2000);
1069
1070 l_display_unit_id number;
1071
1072 l_stmt_deletes varchar2(1000);
1073
1074 l_stmt_updates varchar2(1000);
1075
1076 next_str_pos number;
1077
1078 cur_str varchar2(300);
1079
1080 begin
1081
1082 if p_list is null then
1083 return;
1084 end if;
1085
1086 /*l_list := p_list || ',';*/
1087
1088 msd_dem_common_utilities.log_message('Deleting Price Lists');
1089 msd_dem_common_utilities.log_debug('Deleting Price Lists');
1090
1091 l_list := p_list;
1092
1093 while length(l_list) > 0 loop
1094 next_str_pos := instr(l_list, ',');
1095 cur_str := substr(l_list, 1, next_str_pos-1);
1096 l_list := replace(l_list, cur_str || ',');
1097
1098 l_internal_name := null;
1099
1100 open entities_inuse_cur(cur_str);
1101 fetch entities_inuse_cur into l_internal_name,l_column_name;
1102 close entities_inuse_cur;
1103
1104 if l_internal_name is null then
1105 msd_dem_common_utilities.log_message('Price List ' || cur_str || ' does not exist in Demantra');
1106 msd_dem_common_utilities.log_debug('Price List ' || cur_str || ' does not exist in Demantra');
1107 goto continue;
1108 end if;
1109
1110 begin
1111
1112 l_stmt_prl_display_unit_id := 'select display_units_id ' ||
1113 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
1114 ' where display_units = ''' || l_internal_name || '''';
1115
1116 msd_dem_common_utilities.log_message(l_stmt_prl_display_unit_id);
1117 msd_dem_common_utilities.log_debug(l_stmt_prl_display_unit_id);
1118
1119 open get_prl_display_unit_id for l_stmt_prl_display_unit_id;
1120 fetch get_prl_display_unit_id into l_display_unit_id;
1121 close get_prl_display_unit_id;
1122
1123 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
1124 ' where display_units_id = :1';
1125
1126 msd_dem_common_utilities.log_message(l_stmt_deletes);
1127 msd_dem_common_utilities.log_debug(l_stmt_deletes);
1128
1129 execute immediate l_stmt_deletes using l_display_unit_id;
1130
1131 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
1132 ' where display_units_id = :1';
1133
1134 msd_dem_common_utilities.log_message(l_stmt_deletes);
1135 msd_dem_common_utilities.log_debug(l_stmt_deletes);
1136
1137 execute immediate l_stmt_deletes using l_display_unit_id;
1138
1139 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
1140 ' where display_units_id = :1';
1141
1142 msd_dem_common_utilities.log_message(l_stmt_deletes);
1143 msd_dem_common_utilities.log_debug(l_stmt_deletes);
1144
1145 execute immediate l_stmt_deletes using l_display_unit_id;
1146
1147 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
1148 ' set display_units = :1 where display_units_id = :2';
1149
1150 msd_dem_common_utilities.log_message(l_stmt_updates);
1151 msd_dem_common_utilities.log_debug(l_stmt_updates);
1152
1153 execute immediate l_stmt_updates using l_column_name, l_display_unit_id;
1154
1155 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS') ||
1156 ' set computed_title = :1 ' ||
1157 ' where computed_name = :2 ';
1158
1159 msd_dem_common_utilities.log_message(l_stmt_updates);
1160 msd_dem_common_utilities.log_debug(l_stmt_updates);
1161
1162 execute immediate l_stmt_updates using l_column_name, l_column_name;
1163
1164 l_stmt_deletes := 'delete from msd_dem_entities_inuse' ||
1165 ' where internal_name = :1 and ebs_entity = ''PRL''';
1166
1167 msd_dem_common_utilities.log_message(l_stmt_deletes);
1168 msd_dem_common_utilities.log_debug(l_stmt_deletes);
1169
1170 execute immediate l_stmt_deletes using l_internal_name;
1171
1172 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'SALES_DATA') ||
1173 ' set ' || l_column_name || ' = null';
1174
1175 msd_dem_common_utilities.log_message(l_stmt_updates);
1176 msd_dem_common_utilities.log_debug(l_stmt_updates);
1177
1178 execute immediate l_stmt_updates;
1179
1180 commit;
1181
1182 msd_dem_common_utilities.log_message('Deleted price list ' || l_internal_name);
1183 msd_dem_common_utilities.log_debug('Deleted price list ' || l_internal_name);
1184
1185 exception
1186 when others then
1187 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
1188 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
1189 msd_dem_common_utilities.log_message('Failed deleting price list ' || l_internal_name);
1190 msd_dem_common_utilities.log_debug('Failed deleting price list ' || l_internal_name);
1191 retcode := 1;
1192
1193 end;
1194
1195 <<continue>>
1196 null;
1197
1198 end loop;
1199
1200 retcode := 0;
1201
1202 exception
1203 when others then
1204 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
1205 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
1206 retcode := -1;
1207
1208 end;
1209
1210 END MSD_DEM_COLLECT_PRICE_LISTS;
1211