[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_UOMS
Source
1 PACKAGE BODY MSD_DEM_COLLECT_UOMS AS
2 /* $Header: msddemuomclb.pls 120.3.12000000.2 2007/09/24 11:44:24 nallkuma noship $ */
3
4 function get_lookup_value(p_lookup_type IN VARCHAR2,
5 p_lookup_code IN VARCHAR2)
6 return VARCHAR2
7
8 as
9
10 cursor get_lookup_value is
11 select meaning
12 from fnd_lookup_values
13 where lookup_type = p_lookup_type
14 and lookup_code = p_lookup_code
15 and language = 'US';
16
17 cursor get_schema_name is
18 select fnd_profile.value('MSD_DEM_SCHEMA')
19 from dual;
20
21 l_lookup_value varchar2(200);
22 l_schema_name varchar2(200);
23
24 begin
25
26 open get_lookup_value;
27 fetch get_lookup_value into l_lookup_value;
28 close get_lookup_value;
29
30 if p_lookup_type = 'MSD_DEM_TABLES' then
31
32 open get_schema_name;
33 fetch get_schema_name into l_schema_name;
34 close get_schema_name;
35
36 if l_schema_name is not null then
37 l_lookup_value := l_schema_name || '.' || l_lookup_value;
38 end if;
39
40 end if;
41
42 return l_lookup_value;
43
44 end;
45
46 function msd_dem_uom_conversion (from_unit varchar2,
47 to_unit varchar2,
48 item_id number) return number is
49
50 uom_rate number;
51
52 from_class varchar2(10);
53 to_class varchar2(10);
54
55 CURSOR standard_conversions IS
56 SELECT t.conversion_rate std_to_rate,
57 t.to_uom_class std_to_class,
58 f.conversion_rate std_from_rate,
59 f.from_uom_class std_from_class
60 FROM msd_dem_uom_conversions_gtt t,
61 msd_dem_uom_conversions_gtt f
62 WHERE t.sr_item_pk in (item_id, 0)
63 AND t.to_uom_code = to_unit
64 AND t.to_uom_class = t.from_uom_class
65 AND f.sr_item_pk in (item_id, 0)
66 AND f.to_uom_code = from_unit
67 AND f.to_uom_class = f.from_uom_class
68 ORDER BY t.sr_item_pk DESC,
69 f.sr_item_pk DESC;
70
71
72 std_rec standard_conversions%rowtype;
73
74
75 CURSOR interclass_conversions(p_from_class VARCHAR2, p_to_class VARCHAR2) IS
76 select decode(from_uom_class, p_from_class, 1, 2) from_flag,
77 decode(to_uom_class, p_to_class, 1, 2) to_flag,
78 conversion_rate rate
79 from msd_dem_uom_conversions_gtt
80 where sr_item_pk = item_id and
81 ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
82 (from_uom_class = p_to_class and to_uom_class = p_from_class) );
83
84 class_rec interclass_conversions%rowtype;
85
86 invalid_conversion exception;
87
88 type conv_tab is table of number index by binary_integer;
89 type class_tab is table of varchar2(10) index by binary_integer;
90
91 interclass_rate_tab conv_tab;
92 from_class_flag_tab conv_tab;
93 to_class_flag_tab conv_tab;
94 from_rate_tab conv_tab;
95 to_rate_tab conv_tab;
96 from_class_tab class_tab;
97 to_class_tab class_tab;
98
99 std_index number;
100 class_index number;
101
102 from_rate number := 1;
103 to_rate number := 1;
104 interclass_rate number := 1;
105 to_class_rate number := 1;
106 from_class_rate number := 1;
107 msgbuf varchar2(500);
108
109 begin
110
111 /*
112 ** Conversion between between two UOMS.
113 **
114 ** 1. The conversion always starts from the conversion defined, if exists,
115 ** for an specified item.
116 ** 2. If the conversion id not defined for that specific item, then the
117 ** standard conversion, which is defined for all items, is used.
118 ** 3. When the conversion involves two different classes, then
119 ** interclass conversion is activated.
120 */
121
122 /* If from and to units are the same, conversion rate is 1.
123 Go immediately to the end of the procedure to exit.*/
124
125 if (from_unit = to_unit) then
126 uom_rate := 1;
127 goto procedure_end;
128 end if;
129
130
131 /* Get item specific or standard conversions */
132 open standard_conversions;
133 std_index := 0;
134 loop
135
136 std_index := std_index + 1;
137
138 fetch standard_conversions into std_rec;
139 exit when standard_conversions%notfound;
140
141 from_rate_tab(std_index) := std_rec.std_from_rate;
142 from_class_tab(std_index) := std_rec.std_from_class;
143 to_rate_tab(std_index) := std_rec.std_to_rate;
144 to_class_tab(std_index) := std_rec.std_to_class;
145
146 end loop;
147
148 close standard_conversions;
149
150 if (std_index = 0) then /* No conversions defined */
151 msgbuf := msgbuf||'Invalid standard conversion : ';
152 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
153 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
154 raise invalid_conversion;
155
156 else
157 /* Conversions are ordered.
158 Item specific conversions will be returned first. */
159
160 from_class := from_class_tab(1);
161 to_class := to_class_tab(1);
162 from_rate := from_rate_tab(1);
163 to_rate := to_rate_tab(1);
164
165 end if;
166
167
168 /* Load interclass conversion tables */
169 if (from_class <> to_class) then
170 class_index := 0;
171 open interclass_conversions (from_class, to_class);
172 loop
173
174 fetch interclass_conversions into class_rec;
175 exit when interclass_conversions%notfound;
176
177 class_index := class_index + 1;
178
179 to_class_flag_tab(class_index) := class_rec.to_flag;
180 from_class_flag_tab(class_index) := class_rec.from_flag;
181 interclass_rate_tab(class_index) := class_rec.rate;
182
183 end loop;
184 close interclass_conversions;
185
186 /* No interclass conversion is defined */
187 if (class_index = 0 ) then
188 msgbuf := msgbuf||'Invalid Interclass conversion : ';
189 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
190 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
191 raise invalid_conversion;
192 else
193 if ( to_class_flag_tab(1) = 1 and from_class_flag_tab(1) = 1 ) then
194 to_class_rate := interclass_rate_tab(1);
195 from_class_rate := 1;
196 else
197 from_class_rate := interclass_rate_tab(1);
198 to_class_rate := 1;
199 end if;
200 interclass_rate := from_class_rate/to_class_rate;
201 end if;
202 end if; /* End of from_class <> to_class */
203
204 /*
205 ** conversion rates are defaulted to '1' at the start of the procedure
206 ** so seperate calculations are not required for standard/interclass
207 ** conversions
208 */
209
210 if (to_rate <> 0 ) then
211 uom_rate := (from_rate * interclass_rate) / to_rate;
212 else
213 uom_rate := null;
214 end if;
215
216
217 /* Put a label and a null statement over here so that you can
218 the goto statements can branch here */
219 <<procedure_end>>
220
221 return uom_rate;
222
223 exception
224
225 when others then
226 uom_rate := null;
227 return uom_rate;
228
229 END msd_dem_uom_conversion;
230
231 procedure populate_uom(retcode out nocopy number
232 ,p_uom_code in varchar2
233 ,p_instance_id number)
234
235 as
236
237 cursor get_uom_metadata is
238 select table_name, column_name
239 from msd_dem_entities_inuse
240 where ebs_entity = 'UOM'
241 and demantra_entity = 'DISPLAY_UNIT'
242 and internal_name = p_uom_code;
243
244 l_stmt varchar2(4000);
245
246 l_table_name varchar2(100);
247 l_column_name varchar2(100);
248
249 begin
250
251 open get_uom_metadata;
252 fetch get_uom_metadata into l_table_name, l_column_name;
253
254 if get_uom_metadata%notfound then
255 close get_uom_metadata;
256 msd_dem_common_utilities.log_message('UOM deleted. Please recreate the UOM');
257 msd_dem_common_utilities.log_debug('UOM deleted. Please recreate the UOM');
258 retcode := 1;
259 return;
260 end if;
261
262 close get_uom_metadata;
263
264 if fnd_profile.value('MSD_DEM_SCHEMA') is not null then
265 l_stmt := 'update ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.' || l_table_name || ' tei ';
266 else
267 l_stmt := 'update ' || l_table_name || ' tei ';
268 end if;
269
270 l_stmt := l_stmt || ' set ' || l_column_name || ' = ( ' ||
271 ' select msd_dem_collect_uoms.msd_dem_uom_conversion(msc.uom_code, ''' || p_uom_code || ''', msc.sr_inventory_item_id)' ||
272 ' from msc_system_items msc' ||
273 ' where plan_id = -1 ' ||
274 ' and sr_instance_id = :1 ' ||
275 ' and tei.ebs_item_dest_key = msc.inventory_item_id ' ||
276 ' and rownum < 2 ' ||
277 ')';
278
279
280 msd_dem_common_utilities.log_debug('p_instance_id ' || p_instance_id);
281 msd_dem_common_utilities.log_debug(l_stmt);
282
283 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
284 execute immediate l_stmt using p_instance_id;
285 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
286
287 exception
288 when others then
289 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
290 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
291 retcode := 1;
292
293 end;
294
295
296 procedure process_uom(retcode out nocopy number, p_uom_code in varchar2)
297
298 as
299
300 cursor verify_entities_inuse is
301 select 1 from
302 msd_dem_entities_inuse
303 where internal_name = p_uom_code
304 and ebs_entity = 'UOM';
305
306 type c_get_new_uom_display_unit is ref cursor;
307 get_new_uom_display_unit c_get_new_uom_display_unit;
308 l_stmt_new_uom_display_unit varchar2(2000);
309
310
311 type new_uom_rectype is record(
312 display_units varchar2(500)
313 ,display_units_id number
314 ,data_table varchar2(500)
315 ,data_field varchar2(500)
316 );
317
318 new_uom new_uom_rectype;
319
320 type c_get_component is ref cursor;
321 get_component c_get_component;
322 l_stmt_get_component varchar2(2000);
323
324 type c_get_component_sop is ref cursor;
325 get_component_sop c_get_component_sop;
326 l_stmt_get_component_sop varchar2(2000);
327
328 l_verify_entities_inuse number;
329
330 l_component_id number;
331 l_component_id_sop number;
332
333 l_stmt varchar2(2000);
334
335 begin
336
337 open verify_entities_inuse;
338 fetch verify_entities_inuse into l_verify_entities_inuse;
339 close verify_entities_inuse;
340
341 if l_verify_entities_inuse is null then
342
343 l_stmt_new_uom_display_unit := 'select display_units ,display_units_id ,data_table ,data_field ' ||
344 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
345 ' where display_units_id in ' ||
346 ' (select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
347 ' minus ' ||
348 ' select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') || ' ' || ')' ||
349 ' and display_units like ''%EBSUOM%'' and rownum < 2';
350
351 msd_dem_common_utilities.log_debug(l_stmt_new_uom_display_unit);
352
353 open get_new_uom_display_unit for l_stmt_new_uom_display_unit;
354 fetch get_new_uom_display_unit into new_uom;
355 if get_new_uom_display_unit%notfound then
356 msd_dem_common_utilities.log_message('Seeded Display Units not Available');
357 msd_dem_common_utilities.log_debug('Seeded Display Units not Available');
358 close get_new_uom_display_unit;
359 retcode := 1;
360 return;
361 end if;
362 close get_new_uom_display_unit;
363
364
365 l_stmt := 'insert into msd_dem_entities_inuse(
366 ebs_entity
367 ,demantra_entity
368 ,internal_name
369 ,table_name
370 ,column_name
371 ,last_update_date
372 ,last_updated_by
373 ,creation_date
374 ,created_by
375 ,last_update_login
376 ) values
377 (
378 ''UOM''
379 ,''DISPLAY_UNIT''
380 ,:1
381 ,:2
382 ,:3
383 ,:4
384 ,:5
385 ,:6
386 ,:7
387 ,:8
388 )';
389
390 msd_dem_common_utilities.log_debug(l_stmt);
391
392 execute immediate l_stmt using p_uom_code, new_uom.data_table, new_uom.data_field, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.user_id;
393
394 l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
395 ' set display_units = :1 ' ||
396 ' where display_units_id = :2';
397
398 msd_dem_common_utilities.log_debug(l_stmt);
399
400 execute immediate l_stmt using p_uom_code, new_uom.display_units_id;
401
402 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
403 ' (group_table_id ,display_units_id) ' ||
404 ' ( ' ||
405 ' select group_table_id, :1 ' ||
406 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'GROUP_TABLES') ||
407 ' where group_type = 1 ' ||
408 ' minus ' ||
409 ' select group_table_id, display_units_id ' ||
410 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
411 ' where display_units_id = :2 ' ||
415 execute immediate l_stmt using new_uom.display_units_id, new_uom.display_units_id;
412 ' )';
413
414 msd_dem_common_utilities.log_debug(l_stmt);
416
417 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') || '''';
418
419 msd_dem_common_utilities.log_debug(l_stmt_get_component);
420
421 open get_component for l_stmt_get_component;
422 fetch get_component into l_component_id;
423 close get_component;
424
425 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
426 ' (dcm_product_id ,display_units_id) ' ||
427 ' (select :1, :2 from dual)';
428 msd_dem_common_utilities.log_debug(l_stmt);
429 execute immediate l_stmt using l_component_id,new_uom.display_units_id;
430
431
432 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') || '''';
433
434 msd_dem_common_utilities.log_debug(l_stmt_get_component_sop);
435
436 open get_component_sop for l_stmt_get_component_sop;
437 fetch get_component_sop into l_component_id_sop;
438 close get_component_sop;
439
440 l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
441 ' (dcm_product_id ,display_units_id) ' ||
442 ' (select :1, :2 from dual)';
443 msd_dem_common_utilities.log_debug(l_stmt);
444 execute immediate l_stmt using l_component_id_sop,new_uom.display_units_id;
445
446 else
447 l_stmt := null;
448 l_stmt := 'update msd_dem_entities_inuse set table_name = (select data_table from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :1)' ||
449 ', column_name = (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :2)' ||
450 'where ebs_entity = ''UOM'' and demantra_entity = ''DISPLAY_UNIT'' and internal_name = :3';
451
452 msd_dem_common_utilities.log_debug(l_stmt);
453
454 execute immediate l_stmt using p_uom_code, p_uom_code, p_uom_code;
455
456 end if;
457
458 exception
459 when others then
460 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
461 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
462 retcode := 1;
463
464 end;
465
466 procedure populate_demantra_uom_table(errbuf out nocopy varchar2,
467 retcode out nocopy number,
468 p_instance_id in number,
469 p_include_uom_list in varchar2,
470 p_exclude_uom_list in varchar2)
471
472 as
473
474 type c_uom_code is ref cursor;
475
476 get_uom_code c_uom_code;
477
478 l_stmt varchar2(500);
479 l_list varchar2(500);
480 l_list2 varchar2(500);
481
482 l_uom_code varchar2(30);
483
484 begin
485
486
487 l_stmt := null;
488 l_stmt := 'select distinct from_uom_code uom_code
489 from msd_dem_uom_conversions_gtt
490 union
491 select distinct to_uom_code uom_code
492 from msd_dem_uom_conversions_gtt';
493
494 l_list := null;
495 l_list2 := null;
496
497 if p_include_uom_list is not null then
498
499 l_list := '''' || replace(p_include_uom_list, ',', ''',''') || '''';
500 l_list2 := p_include_uom_list;
501
502 elsif p_exclude_uom_list is not null then
503
504 l_list := '''' || replace(p_exclude_uom_list, ',', ''',''') || '''';
505
506 end if;
507
508 l_list2 := l_list2 || ',';
509
510 if l_list is not null then
511
512 l_stmt := 'select uom_code from (' || l_stmt;
513 l_stmt := l_stmt || ')' || 'where uom_code ';
514
515 if p_include_uom_list is null then
516 l_stmt := l_stmt || ' not ';
517 end if;
518
519 l_stmt := l_stmt || ' in ' || '(' || l_list || ')';
520
521 end if;
522
523
524
525 open get_uom_code for l_stmt;
526
527 loop
528
529 fetch get_uom_code into l_uom_code;
530 exit when get_uom_code%notfound;
531
532 msd_dem_common_utilities.log_message('Populating UOM: ' || l_uom_code);
533 msd_dem_common_utilities.log_debug('Populating UOM: ' || l_uom_code);
534
535 l_list2 := replace(l_list2, l_uom_code||',' , '');
536
537 process_uom(retcode, l_uom_code);
538
539 if retcode = -1 or retcode = 1 then
540 msd_dem_common_utilities.log_message('Failed processing UOM: ' || l_uom_code);
541 msd_dem_common_utilities.log_debug('Failed Processing UOM: ' || l_uom_code);
542 goto continue;
543 end if;
544
545 populate_uom(retcode, l_uom_code, p_instance_id);
546
547 <<continue>>
548 null;
549
550 end loop;
551
552 close get_uom_code;
553
554 l_list2 := rtrim(l_list2, ',');
555
556 if l_list2 is not null then
557 retcode := 1;
558 msd_dem_common_utilities.log_message('Following UOM''s dont exist in source: ' || l_list2);
559 msd_dem_common_utilities.log_debug('Following UOM''s dont exist in source: ' || l_list2);
560 end if;
561
562 exception
563 when others then
564 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
565 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
566 retcode := 1;
567
568 end;
569
573
570 procedure collect_from_source(errbuf out nocopy varchar2,
571 retcode out nocopy number,
572 p_instance_id in number)
574 as
575
576 l_stmt varchar2(4000);
577 l_key_values varchar2(4000);
578
579 l_retcode number;
580
581 begin
582
583 /*msd_dem_query_utilities.get_query(retcode, l_stmt, 'MSD_DEM_UOM_FROM_SOURCE', p_instance_id);*/
584
585 l_key_values := '$C_INSTANCE#' || p_instance_id || '$';
586
587 msd_dem_query_utilities.get_query2 (
588 l_retcode,
589 l_stmt,
590 'MSD_DEM_UOM_FROM_SOURCE',
591 p_instance_id,
592 l_key_values,
593 0,
594 null);
595
596 if l_stmt is null then
597
598 msd_dem_common_utilities.log_message('Cannot find query to get UOM conversions from source');
599 msd_dem_common_utilities.log_debug('Cannot find query to get UOM conversions from source');
600 retcode := -1;
601 return;
602
603 end if;
604
605 msd_dem_common_utilities.log_debug('Query: ' || l_stmt);
606
607 msd_dem_common_utilities.log_debug('p_instance_id ' || p_instance_id);
608
609 msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
610 execute immediate l_stmt;
611 msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
612
613 exception
614 when others then
615 errbuf := substr(SQLERRM,1,150);
616 msd_dem_common_utilities.log_message(errbuf);
617 msd_dem_common_utilities.log_debug(errbuf);
618 retcode := -1;
619
620
621 end;
622
623 procedure collect_uom(errbuf out nocopy varchar2,
624 retcode out nocopy number,
625 p_instance_id in number,
626 p_include_all in number,
627 p_include_uom_list in varchar2,
628 p_exclude_uom_list in varchar2)
629
630 as
631
632 begin
633
634 if p_include_all = 1 and (p_include_uom_list is not null or p_exclude_uom_list is not null) then
635 msd_dem_common_utilities.log_message('Cannot specify both collect all and include or exclude list');
636 msd_dem_common_utilities.log_debug('Cannot specify both collect all and include or exclude list');
637 retcode := -1;
638 return;
639 end if;
640
641 if p_include_all = 2 and p_include_uom_list is null and p_exclude_uom_list is null then
642
643 msd_dem_common_utilities.log_message('Exactly one of the parameters Include UOM''s or Exclude UOM''s must be specified, when Collect All UOM''s is No');
644 msd_dem_common_utilities.log_debug('Exactly one of the parameters Include UOM''s or Exclude UOM''s must be specified, when Collect All UOM''s is No');
645 retcode := -1;
646 return;
647
648 end if;
649
650 if p_include_all = 2 and p_include_uom_list is not null and p_exclude_uom_list is not null then
651
652 msd_dem_common_utilities.log_message('Should not specify both include and exclude list');
653 msd_dem_common_utilities.log_debug('Should not specify both include and exclude list');
654 retcode := -1;
655 return;
656
657 end if;
658
659 msd_dem_common_utilities.log_message('Collecting UOM''s');
660 msd_dem_common_utilities.log_debug('Collecting UOM''s');
661
662 msd_dem_common_utilities.log_message('Collecting UOM conversions from source');
663 msd_dem_common_utilities.log_debug('Collecting UOM conversions from source');
664 collect_from_source(errbuf, retcode, p_instance_id);
665
666 if retcode = -1 then
667 goto error_handle;
668 end if;
669
670
671 msd_dem_common_utilities.log_message('Populating UOM''s in Demand Planning components');
672 msd_dem_common_utilities.log_debug('Populating UOM''s in Demand Planning components');
673 populate_demantra_uom_table(errbuf, retcode, p_instance_id, p_include_uom_list, p_exclude_uom_list);
674
675 commit;
676
677 if retcode <> -1 and retcode <> 1 then
678 retcode := 0;
679 end if;
680 return;
681
682 <<error_handle>>
683 retcode := -1;
684
685 exception
686 when others then
687 errbuf := substr(SQLERRM,1,150);
688 msd_dem_common_utilities.log_message(errbuf);
689 msd_dem_common_utilities.log_debug(errbuf);
690 retcode := -1;
691
692
693 end;
694
695 END MSD_DEM_COLLECT_UOMS;
696