[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.5.12010000.2 2008/09/05 10:10:56 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 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES') || ' tqs set tqs.load_option = 0, tqs.purge_option = 0 '
127 || ' where tqs.id = (select tq.id from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST') || ' tl '
128 || ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
129 || ' where tl.name = :1 '
130 || ' and tq.transfer_id = tl.id) and tqs.series_id = ' || l_series_id;
131
132 msd_dem_common_utilities.log_debug(l_stmt);
133
134 execute immediate l_stmt using fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE');
135
136 <<continue>>
137
138 null;
139
140 exception
141 when others then
142 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
143 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
144 retcode := 1;
145
146 end;
147
148 procedure process_prl(retcode out nocopy number, p_prl_code in varchar2)
149
150 as
151
152 cursor verify_entities_inuse is
153 select 1 from
154 msd_dem_entities_inuse
155 where internal_name = p_prl_code
156 and ebs_entity = 'PRL';
157
158 type c_get_new_prl_display_unit is ref cursor;
159 get_new_prl_display_unit c_get_new_prl_display_unit;
160 l_stmt_new_prl_display_unit varchar2(2000);
161
162 type c_get_component is ref cursor;
163 get_component c_get_component;
164 l_stmt_get_component varchar2(2000);
165
166 type c_get_component_sop is ref cursor;
167 get_component_sop c_get_component_sop;
168 l_stmt_get_component_sop varchar2(2000);
169
170 type c_get_profile_table is ref cursor;
171 get_profile_table c_get_profile_table;
172 l_stmt_get_profile_table varchar2(2000);
173
174 type c_get_seeded_unit is ref cursor;
175 get_seeded_unit c_get_seeded_unit;
176 l_stmt_get_seeded_unit varchar2(2000);
177
178 l_get_seeded_unit varchar2(250);
179
180 l_profile_table_name varchar2(1000);
181
182 l_verify_entities_inuse number;
183
184 type new_prl_rectype is record(
185 display_units varchar2(500)
186 ,display_units_id number
187 ,data_table varchar2(500)
188 ,data_field varchar2(500)
189 );
190
191 new_prl new_prl_rectype;
192
193 l_component_id number;
194 l_component_id_sop number;
195
196 l_stmt varchar2(2000);
197
198 begin
199
200 l_stmt_get_seeded_unit := 'select display_units from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
201 ' where display_units = ''' || p_prl_code || '''';
202
203 open get_seeded_unit for l_stmt_get_seeded_unit;
204 fetch get_seeded_unit into l_get_seeded_unit;
205 close get_seeded_unit;
206
207 open verify_entities_inuse;
208 fetch verify_entities_inuse into l_verify_entities_inuse;
209 close verify_entities_inuse;
210
211 if l_verify_entities_inuse is null then
212
213 if l_get_seeded_unit is not null then
214 msd_dem_common_utilities.log_message('Seeded Display Unit with name ' || p_prl_code || ' exist in Demantra. This Price List will not created');
215 msd_dem_common_utilities.log_debug('Seeded Display Unit with name ' || p_prl_code || ' exist in Demantra. This Price List will not created');
216 retcode := 1;
217 return;
218 end if;
219
220 l_stmt_new_prl_display_unit := 'select display_units ,display_units_id ,data_table ,data_field ' ||
221 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
222 ' where display_units_id in ' ||
223 ' (select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
224 ' minus ' ||
225 ' select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') || ' ' || ')' ||
226 ' and display_units like ''%EBSPRICELIST%'' and rownum < 2';
227
228 msd_dem_common_utilities.log_debug(l_stmt_new_prl_display_unit);
229
230
231 open get_new_prl_display_unit for l_stmt_new_prl_display_unit;
232 fetch get_new_prl_display_unit into new_prl;
233 if get_new_prl_display_unit%notfound then
234 msd_dem_common_utilities.log_message('Seeded Display Units for Price List not Available');
235 msd_dem_common_utilities.log_debug('Seeded Display Units for Price List not Available');
236 close get_new_prl_display_unit;
237 retcode := 1;
238 return;
239 end if;
240 close get_new_prl_display_unit;
241
242 l_stmt_get_profile_table := 'select tq.table_name from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST') || ' tl '
243 || ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
244 || ' where tl.name = ''' || fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE') || ''''
245 || ' and tq.transfer_id = tl.id';
246
247
248 open get_profile_table for l_stmt_get_profile_table;
249 fetch get_profile_table into l_profile_table_name;
250 close get_profile_table;
251
252
253
254 l_stmt := 'insert into msd_dem_entities_inuse(
255 ebs_entity
256 ,demantra_entity
257 ,internal_name
258 ,table_name
259 ,column_name
260 ,last_update_date
261 ,last_updated_by
262 ,creation_date
263 ,created_by
264 ,last_update_login
265 ) values
266 (
267 ''PRL''
268 ,''DISPLAY_UNIT''
269 ,:1
270 ,:2
271 ,:3
272 ,:4
273 ,:5
274 ,:6
275 ,:7
276 ,:8
277 )';
278
279 msd_dem_common_utilities.log_debug(l_stmt);
280
281
282 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;
283
284 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
285 ' set display_units = :1 ' ||
286 ' where display_units_id = :2';
287
288 msd_dem_common_utilities.log_debug(l_stmt);
289
290 execute immediate l_stmt using p_prl_code, new_prl.display_units_id;
291
292 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
293 ' (group_table_id ,display_units_id )' ||
294 ' (select group_table_id, :1 ' ||
295 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'GROUP_TABLES') ||
296 ' where group_type = 1 ' ||
297 ' )';
298
299 msd_dem_common_utilities.log_debug(l_stmt);
300
301 execute immediate l_stmt using new_prl.display_units_id;
302
303 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') || '''';
304
305 msd_dem_common_utilities.log_debug(l_stmt_get_component);
306
307 open get_component for l_stmt_get_component;
308 fetch get_component into l_component_id;
309 close get_component;
310
311 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
312 ' (dcm_product_id ,display_units_id) ' ||
313 ' (select :1, :2 from dual)';
314
315 msd_dem_common_utilities.log_debug(l_stmt);
316 execute immediate l_stmt using l_component_id,new_prl.display_units_id;
317
318 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
319 ' (display_units_id, real_value_id) ' ||
320 ' (select :1, real_value_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_INDEX') || ' dpi ' ||
321 ' where dpi.dcm_product_id = ' || l_component_id ||
322 ')';
323
324 msd_dem_common_utilities.log_debug(l_stmt);
325
326 execute immediate l_stmt using new_prl.display_units_id;
327
328
329
330 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') || '''';
331
332 msd_dem_common_utilities.log_debug(l_stmt_get_component_sop);
333
334 open get_component_sop for l_stmt_get_component_sop;
335 fetch get_component_sop into l_component_id_sop;
336 close get_component_sop;
337
338 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
339 ' (dcm_product_id ,display_units_id) ' ||
340 ' (select :1, :2 from dual)';
341
342 msd_dem_common_utilities.log_debug(l_stmt);
343 execute immediate l_stmt using l_component_id_sop,new_prl.display_units_id;
344
345 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS')
346 || ' set computed_title = :1 '
347 || ' where computed_name = :2 ';
348
349 execute immediate l_stmt using substr(p_prl_code, 1 , 50), new_prl.data_field;
350
351
352 /*else
353 l_stmt := null;
354 l_stmt := 'update msd_dem_entities_inuse set table_name = ''biio_ebs_price_list''' ||
355 ', column_name = (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :1)' ||
356 'where ebs_entity = ''PRL'' and demantra_entity = ''DISPLAY_UNIT'' and internal_name = :2';
357
358 msd_dem_common_utilities.log_debug(l_stmt);
359
360 execute immediate l_stmt using p_prl_code, p_prl_code; */
361
362 end if;
363
364
365
366 exception
367 when others then
368 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
369 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
370 retcode := 1;
371
372 end;
373
374 procedure populate_demantra_prl_table(errbuf out nocopy varchar2,
375 retcode out nocopy number,
376 p_instance_id in number,
377 p_include_prl_list in varchar2,
378 p_exclude_prl_list in varchar2,
379 p_start_date in date,
380 p_end_date in date)
381
382 as
383
384 type c_prl_code is ref cursor;
385
386 get_prl_code c_prl_code;
387
388 l_stmt varchar2(500);
389 l_list2 varchar2(500);
390
391 l_prl_code varchar2(250);
392
393 was_retcode_1 number := 0;
394
395 begin
396
397 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY_SERIES') || ' tqs set tqs.load_option = 2, tqs.purge_option = 0 '
398 || ' where tqs.id = (select tq.id from ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST') || ' tl '
399 || ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
400 || ' where tl.name = :1 '
401 || ' and tq.transfer_id = tl.id)';
402
403 msd_dem_common_utilities.log_debug(l_stmt);
404
405 execute immediate l_stmt using fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE');
406
407
408 l_stmt := null;
409 l_stmt := 'select distinct price_list_name price_list_name
413 /*l_list2 := null;
410 from msd_dem_price_lists_gtt';
411
412
414
415 if p_include_prl_list is not null then
416
417 l_list2 := p_include_prl_list;
418
419 end if;
420
421 l_list2 := l_list2 || ',';*/
422
423 l_list2 := v_list;
424 l_list2 := replace(l_list2, '''', '');
425 l_list2 := l_list2 || ',';
426
427 if v_list is not null then
428
429 l_stmt := 'select price_list_name from (' || l_stmt;
430 l_stmt := l_stmt || ')' || 'where price_list_name ';
431
432 if p_exclude_prl_list is not null then
433 l_stmt := l_stmt || ' not ';
434 end if;
435
436 l_stmt := l_stmt || ' in ' || '(' || v_list || ')';
437
438 end if;
439
440
441
442 open get_prl_code for l_stmt;
443
444 loop
445
446 fetch get_prl_code into l_prl_code;
447 exit when get_prl_code%notfound;
448
449 msd_dem_common_utilities.log_message('Populating Price List: ' || l_prl_code);
450 msd_dem_common_utilities.log_debug('Populating Price List: ' || l_prl_code);
451
452 l_list2 := replace(l_list2, l_prl_code||',' , '');
453
454 process_prl(retcode, l_prl_code);
455
456 if retcode = -1 or retcode = 1 then
457 msd_dem_common_utilities.log_message('Failed processing Price List: ' || l_prl_code);
458 msd_dem_common_utilities.log_debug('Failed Processing Price List: ' || l_prl_code);
459 was_retcode_1 := retcode;
460 goto continue;
461 end if;
462
463 populate_prl(retcode, l_prl_code, p_instance_id, p_start_date, p_end_date);
464
465 if retcode = -1 or retcode = 1 then
466 was_retcode_1 := retcode;
467 end if;
468
469 <<continue>>
470 null;
471
472 end loop;
473
474 close get_prl_code;
475
476 retcode := was_retcode_1;
477
478 l_list2 := rtrim(l_list2, ',');
479
480 if l_list2 is not null then
481 retcode := 1;
482 msd_dem_common_utilities.log_message('Following Price Lists dont exist in source: ' || l_list2);
483 msd_dem_common_utilities.log_debug('Following Price Lists dont exist in source: ' || l_list2);
484 end if;
485
486
487 exception
488 when others then
489 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
490 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
491 retcode := 1;
492
493 end;
494
495 procedure filter_from_list(errbuf out nocopy varchar2,
496 retcode out nocopy number,
497 p_instance_id in number,
498 p_include_prl_list in varchar2,
499 p_exclude_prl_list in varchar2)
500 as
501
502 cursor count_price_lists is
503 select count(*)
504 from msd_dem_price_lists;
505
506 l_count_price_lists number;
507
508 cursor price_list_cur is
509 select price_list_name
510 from msd_dem_price_lists;
511
512 acc_list varchar2(5000);
513 unacc_list varchar2(5000);
514
515 begin
516
517 open count_price_lists;
518 fetch count_price_lists into l_count_price_lists;
519 close count_price_lists;
520
521 if l_count_price_lists = 0 then
522 retcode := -1;
523 return;
524 end if;
525
526 unacc_list := v_list || ',';
527 acc_list := null;
528
529 for price_list_cur_rec in price_list_cur loop
530
531 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
532
533 acc_list := acc_list || '''' || price_list_cur_rec.price_list_name || ''',';
534 unacc_list := replace(unacc_list,'''' || price_list_cur_rec.price_list_name || ''',','');
535
536 elsif (instr(v_list, price_list_cur_rec.price_list_name) = 0 and p_exclude_prl_list is not null) then
537 acc_list := acc_list || '''' || price_list_cur_rec.price_list_name || ''',';
538 unacc_list := replace(unacc_list,'''' || price_list_cur_rec.price_list_name || ''',','');
539
540 end if;
541 end loop;
542
543 if acc_list is not null then
544 acc_list := rtrim(acc_list, ',');
545 end if;
546
547 unacc_list := rtrim(unacc_list, ',');
548 unacc_list := replace(unacc_list, '''', '');
549
550 if unacc_list is not null then
551 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);
552 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);
553
554 if acc_list is not null then
555 retcode := 1;
556 else
557 retcode := 0 ;
558 return;
559 end if;
560 end if;
561
562 v_list := acc_list;
563
564 if retcode <> -1 and retcode <> 1 then
565 retcode := 0;
566 end if;
567
568 exception
569 when others then
570 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
571 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
572 retcode := -1;
573
574 end;
575
576 procedure collect_from_source(errbuf out nocopy varchar2,
580 p_exclude_prl_list in varchar2)
577 retcode out nocopy number,
578 p_instance_id in number,
579 p_include_prl_list in varchar2,
581
582 as
583
584 l_add_where_clause varchar2(5000);
585 l_stmt varchar2(6000);
586 l_key_values varchar2(4000);
587
588 l_retcode number;
589
590 begin
591
592 v_list := null;
593 if p_include_prl_list is not null then
594
595 v_list := '''' || replace(p_include_prl_list, ',', ''',''') || '''';
596
597 elsif p_exclude_prl_list is not null then
598
599 v_list := '''' || replace(p_exclude_prl_list, ',', ''',''') || '''';
600
601 end if;
602
603 filter_from_list(errbuf, retcode, p_instance_id, p_include_prl_list, p_exclude_prl_list);
604
605
606 if retcode = -1 then
607 msd_dem_common_utilities.log_message('Price list collection cannot continue as no price lists are selected in the price list form');
608 msd_dem_common_utilities.log_debug('Price list collection cannot continue as no price lists are selected in the price list form');
609 retcode := -1;
610 return;
611 end if;
612
613 if retcode = 0 then
614 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.');
615 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.');
616 retcode := 0;
617 return;
618 end if;
619
620 l_add_where_clause := null;
621
622 if v_list is not null then
623
624 l_add_where_clause := 'AND qplh.name ';
625
626 --if p_exclude_prl_list is not null then
627 -- l_add_where_clause := l_add_where_clause || ' not ';
628 --end if;
629
630 l_add_where_clause := l_add_where_clause || ' in ' || '(' || v_list || ') ';
631
632 end if;
633
634 l_stmt := null;
635
636 if l_add_where_clause is null then
637 l_add_where_clause := ' and 1=1 ';
638 end if;
639
640 /*msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_PRICE_LIST_FROM_SOURCE', p_instance_id, null, l_add_where_clause ); */
641
642 l_key_values := '$C_INSTANCE#' || p_instance_id ||
643 '$C_ADD_WHERE_CLAUSE#' || l_add_where_clause || '$';
644
645 msd_dem_query_utilities.get_query2 (
646 l_retcode,
647 l_stmt,
648 'MSD_DEM_PRL_FROM_SOURCE',
649 p_instance_id,
650 l_key_values,
651 0,
652 null);
653
654 msd_dem_common_utilities.log_debug('Bind variables: ');
655 msd_dem_common_utilities.log_debug('p_instance_id: ' || p_instance_id);
656 msd_dem_common_utilities.log_debug('p_instance_id: ' || p_instance_id);
657
658 msd_dem_common_utilities.log_debug('Executed Statement: ');
659 msd_dem_common_utilities.log_debug(l_stmt);
660
661 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
662 execute immediate l_stmt;
663 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
664
665 exception
666 when others then
667 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
668 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
669 retcode := -1;
670
671 end;
672
673 procedure collect_price_lists(errbuf out nocopy varchar2,
674 retcode out nocopy number,
675 p_instance_id in number,
676 p_start_date in varchar2,
677 p_end_date in varchar2,
678 p_include_all in number,
679 p_include_prl_list in varchar2,
680 p_exclude_prl_list in varchar2)
681
682 as
683
684 retcode_store number;
685
686 /* Bug# 6459467 - Refresh the price list data profile */
687 x_profile_id NUMBER := NULL;
688 x_stmt VARCHAR2(4000) := NULL;
689
690 begin
691
692 msd_dem_push_setup_parameters.push_setup_parameters(errbuf, retcode, p_instance_id, '-999');
693 if retcode = -1 then
694 msd_dem_common_utilities.log_message('Push Setup Parameters Failed');
695 msd_dem_common_utilities.log_debug('Push Setup Parameters Failed');
696 goto final;
697 end if;
698
699 if p_include_all = 1 and (p_include_prl_list is not null or p_exclude_prl_list is not null) then
700 msd_dem_common_utilities.log_message('Cannot specify both collect all and include or exclude list');
701 msd_dem_common_utilities.log_debug('Cannot specify both collect all and include or exclude list');
702 retcode := -1;
703 return;
704 end if;
705
706 if p_include_all = 2 and p_include_prl_list is null and p_exclude_prl_list is null then
707
708 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');
712
709 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');
710 retcode := -1;
711 return;
713 end if;
714
715 if p_include_all = 2 and p_include_prl_list is not null and p_exclude_prl_list is not null then
716
717 msd_dem_common_utilities.log_message('Should not specify both include and exclude list');
718 msd_dem_common_utilities.log_debug('Should not specify both include and exclude list');
719 retcode := -1;
720 return;
721
722 end if;
723
724 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
725 msd_dem_common_utilities.log_message('From Date should not be greater than To Date');
726 msd_dem_common_utilities.log_debug('From Date should not be greater than To Date');
727 retcode := -1;
728 return;
729 end if;
730
731 msd_dem_common_utilities.log_message('Collecting Price Lists');
732 msd_dem_common_utilities.log_debug('Collecting Price Lists');
733
734 msd_dem_common_utilities.log_message('Collecting Price Lists from source');
735 msd_dem_common_utilities.log_debug('Collecting Price Lists from source');
736 collect_from_source(errbuf, retcode, p_instance_id, p_include_prl_list, p_exclude_prl_list);
737
738 if retcode = -1 then
739 return;
740 end if;
741
742 retcode_store := retcode;
743
744 msd_dem_common_utilities.log_message('Populating Price Lists in Demand Planning components');
745 msd_dem_common_utilities.log_debug('Populating Price Lists in Demand Planning components');
746 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),
747 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')));
748
749
750 /* Bug# 6459467 - Refresh the price list data profile */
751 BEGIN
752 x_stmt:= 'SELECT tq.id FROM ' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_LIST') || ' tl '
753 || ' ,' || get_lookup_value('MSD_DEM_TABLES', 'TRANSFER_QUERY') || ' tq '
754 || ' WHERE tl.name = ''' || fnd_profile.value('MSD_DEM_PRICE_LIST_PROFILE') || ''''
755 || ' AND tq.transfer_id = tl.id';
756
757 msd_dem_common_utilities.log_debug ('Get data profile id stmt : ' || x_stmt);
758 EXECUTE IMMEDIATE x_stmt INTO x_profile_id;
759
760 x_stmt := 'BEGIN '
761 || fnd_profile.value('MSD_DEM_SCHEMA') || '.'
762 || 'API_NOTIFY_APS_INTEGRATION('
763 || x_profile_id
764 ||'); end;';
765
766 msd_dem_common_utilities.log_debug ('Refresh data profile stmt : ' || x_stmt);
767 EXECUTE IMMEDIATE x_stmt;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 msd_dem_common_utilities.log_message('WARNING: Failed to update the price list data profile');
772 END;
773
774 commit;
775
776 if retcode <> -1 and retcode <> 1 then
777 retcode := 0;
778 end if;
779
780 if retcode = 0 then
781 retcode := retcode_store;
782 end if;
783
784 <<final>>
785
786 null;
787
788 exception
789 when others then
790 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
791 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
792 retcode := -1;
793
794 end;
795
796 procedure delete_price_lists(errbuf out nocopy varchar2,
797 retcode out nocopy number,
798 p_list in varchar2)
799 as
800
801 cursor entities_inuse_cur(p_price_list_name varchar2) is
802 select internal_name, column_name
803 from msd_dem_entities_inuse
804 where internal_name = p_price_list_name
805 and ebs_entity = 'PRL';
806
807 l_internal_name varchar2(240);
808 l_column_name varchar2(240);
809
810 l_list varchar2(2000);
811
812 type c_get_prl_display_unit_id is ref cursor;
813 get_prl_display_unit_id c_get_prl_display_unit_id;
814 l_stmt_prl_display_unit_id varchar2(2000);
815
816 l_display_unit_id number;
817
818 l_stmt_deletes varchar2(1000);
819
820 l_stmt_updates varchar2(1000);
821
822 next_str_pos number;
823
824 cur_str varchar2(300);
825
826 begin
827
828 if p_list is null then
829 return;
830 end if;
831
832 /*l_list := p_list || ',';*/
833
834 msd_dem_common_utilities.log_message('Deleting Price Lists');
835 msd_dem_common_utilities.log_debug('Deleting Price Lists');
836
837 l_list := p_list;
838
839 while length(l_list) > 0 loop
840 next_str_pos := instr(l_list, ',');
841 cur_str := substr(l_list, 1, next_str_pos-1);
842 l_list := replace(l_list, cur_str || ',');
843
844 l_internal_name := null;
845
846 open entities_inuse_cur(cur_str);
847 fetch entities_inuse_cur into l_internal_name,l_column_name;
848 close entities_inuse_cur;
849
850 if l_internal_name is null then
851 msd_dem_common_utilities.log_message('Price List ' || cur_str || ' does not exist in Demantra');
852 msd_dem_common_utilities.log_debug('Price List ' || cur_str || ' does not exist in Demantra');
853 goto continue;
854 end if;
855
856 begin
857
858 l_stmt_prl_display_unit_id := 'select display_units_id ' ||
859 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
860 ' where display_units = ''' || l_internal_name || '''';
861
862 msd_dem_common_utilities.log_message(l_stmt_prl_display_unit_id);
863 msd_dem_common_utilities.log_debug(l_stmt_prl_display_unit_id);
864
865 open get_prl_display_unit_id for l_stmt_prl_display_unit_id;
866 fetch get_prl_display_unit_id into l_display_unit_id;
867 close get_prl_display_unit_id;
868
869 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
870 ' where display_units_id = :1';
871
872 msd_dem_common_utilities.log_message(l_stmt_deletes);
873 msd_dem_common_utilities.log_debug(l_stmt_deletes);
874
875 execute immediate l_stmt_deletes using l_display_unit_id;
876
877 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
878 ' where display_units_id = :1';
879
880 msd_dem_common_utilities.log_message(l_stmt_deletes);
881 msd_dem_common_utilities.log_debug(l_stmt_deletes);
882
883 execute immediate l_stmt_deletes using l_display_unit_id;
884
885 l_stmt_deletes := 'delete from ' || get_lookup_value('MSD_DEM_TABLES', 'INDEXES_FOR_UNITS') ||
886 ' where display_units_id = :1';
887
888 msd_dem_common_utilities.log_message(l_stmt_deletes);
889 msd_dem_common_utilities.log_debug(l_stmt_deletes);
890
891 execute immediate l_stmt_deletes using l_display_unit_id;
892
893 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
894 ' set display_units = :1 where display_units_id = :2';
895
896 msd_dem_common_utilities.log_message(l_stmt_updates);
897 msd_dem_common_utilities.log_debug(l_stmt_updates);
898
899 execute immediate l_stmt_updates using l_column_name, l_display_unit_id;
900
901 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'COMPUTED_FIELDS') ||
902 ' set computed_title = :1 ' ||
903 ' where computed_name = :2 ';
904
905 msd_dem_common_utilities.log_message(l_stmt_updates);
906 msd_dem_common_utilities.log_debug(l_stmt_updates);
907
908 execute immediate l_stmt_updates using l_column_name, l_column_name;
909
910 l_stmt_deletes := 'delete from msd_dem_entities_inuse' ||
911 ' where internal_name = :1 and ebs_entity = ''PRL''';
912
913 msd_dem_common_utilities.log_message(l_stmt_deletes);
914 msd_dem_common_utilities.log_debug(l_stmt_deletes);
915
916 execute immediate l_stmt_deletes using l_internal_name;
917
918 l_stmt_updates := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'SALES_DATA') ||
919 ' set ' || l_column_name || ' = null';
920
921 msd_dem_common_utilities.log_message(l_stmt_updates);
922 msd_dem_common_utilities.log_debug(l_stmt_updates);
923
924 execute immediate l_stmt_updates;
925
926 commit;
927
928 msd_dem_common_utilities.log_message('Deleted price list ' || l_internal_name);
929 msd_dem_common_utilities.log_debug('Deleted price list ' || l_internal_name);
930
931 exception
932 when others then
933 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
934 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
935 msd_dem_common_utilities.log_message('Failed deleting price list ' || l_internal_name);
936 msd_dem_common_utilities.log_debug('Failed deleting price list ' || l_internal_name);
937 retcode := 1;
938
939 end;
940
941 <<continue>>
942 null;
943
944 end loop;
945
946 retcode := 0;
947
948 exception
949 when others then
950 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
951 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
952 retcode := -1;
953
954 end;
955
956 END MSD_DEM_COLLECT_PRICE_LISTS;
957