eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.MSD_DEM_COLLECT_UOMS

Source


1 PACKAGE BODY MSD_DEM_COLLECT_UOMS AS
2 /* $Header: msddemuomclb.pls 120.3.12010000.6 2009/04/03 13:28:55 nallkuma ship $ */
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                 -- Bug#7199587    syenamar
344                 -- Use 'data_field' field to look for empty dummy UOMs, 'display_units' field may contain values in any supported language other than english
345 				l_stmt_new_uom_display_unit := 'select display_units ,display_units_id ,data_table ,data_field ' ||
346 																			 ' from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
347 																			 ' where display_units_id in ' ||
348 																			 '  (select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' ' ||
349 			 																 '   minus ' ||
350 			 																 '   select distinct display_units_id from ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') || ' ' || ')' ||
351                                        ' and data_field in ' ||
352                                        '  (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where data_field like ''EBSUOM%'' ' ||
353 			 																 '   minus ' ||
354 			 																 '   select column_name from msd_dem_entities_inuse where ebs_entity = ''UOM'' )' ||
355 																			 ' and rownum < 2';
356 
357 				msd_dem_common_utilities.log_debug(l_stmt_new_uom_display_unit);
358                 -- syenamar
359 
360 				open get_new_uom_display_unit for l_stmt_new_uom_display_unit;
361 				fetch get_new_uom_display_unit into new_uom;
362 				if get_new_uom_display_unit%notfound then
363 					msd_dem_common_utilities.log_message('Seeded Display Units not Available');
364 					msd_dem_common_utilities.log_debug('Seeded Display Units not Available');
365 					close get_new_uom_display_unit;
366 					retcode := 1;
367 					return;
368 				end if;
369 				close get_new_uom_display_unit;
370 
371 
372 				l_stmt := 'insert into msd_dem_entities_inuse(
373 									 ebs_entity
374                    ,demantra_entity
375                    ,internal_name
376                    ,table_name
377                    ,column_name
378                    ,last_update_date
379 	                 ,last_updated_by
380 	                 ,creation_date
381 	                 ,created_by
382 	                 ,last_update_login
383                    ) values
384                    (
385                    ''UOM''
386                    ,''DISPLAY_UNIT''
387                    ,:1
388                    ,:2
389                    ,:3
390                    ,:4
391                    ,:5
392                    ,:6
393                    ,:7
394                    ,:8
395                    )';
396 
397 				msd_dem_common_utilities.log_debug(l_stmt);
398 
399 				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;
400 
401 				l_stmt := 'update ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
402 									' set display_units = :1 ' ||
403 									' where display_units_id = :2';
404 
405 				msd_dem_common_utilities.log_debug(l_stmt);
406 
407 				execute immediate l_stmt using p_uom_code, new_uom.display_units_id;
408 
409 				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
410 									' (group_table_id ,display_units_id) ' ||
411 									' ( ' ||
412 									' select group_table_id, :1 ' ||
413 									' from ' || get_lookup_value('MSD_DEM_TABLES', 'GROUP_TABLES') ||
414 								  ' where group_type = 1 ' ||
415 									' minus ' ||
416 									' select group_table_id, display_units_id ' ||
417 									' from ' || get_lookup_value('MSD_DEM_TABLES', 'AVAIL_UNITS') ||
418 									' where  display_units_id  = :2 ' ||
419 									' )';
420 
421 				msd_dem_common_utilities.log_debug(l_stmt);
422 				execute immediate l_stmt using new_uom.display_units_id, new_uom.display_units_id;
423 
424                 -- Bug#7199587    syenamar
425                 -- Use component id obtained from lookup
426 
427                 /*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') || '''';
428 
429 				msd_dem_common_utilities.log_debug(l_stmt_get_component);
430 
431 				open get_component for l_stmt_get_component;
432 				fetch get_component into l_component_id;
433 				close get_component;*/
434 
435         /* Bug#8224935 - APP ID */ -- nallkuma
436           l_component_id := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
437                                                                                'COMP_DM',
438                                                                                1,
439                                                                                'dcm_product_id'));
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,new_uom.display_units_id;
445 
446 
447 				/*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') || '''';
448 
449 				msd_dem_common_utilities.log_debug(l_stmt_get_component_sop);
450 
451 				open get_component_sop for l_stmt_get_component_sop;
452 				fetch get_component_sop into l_component_id_sop;
453 				close get_component_sop;*/
454 
455 				/* Bug#8224935 - APP ID */ -- nallkuma
456         l_component_id_sop := to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
457                                                                                   'COMP_SOP',
458                                                                                   1,
459                                                                                   'dcm_product_id'));
460 				l_stmt := 'insert into ' || get_lookup_value('MSD_DEM_TABLES', 'DCM_PRODUCTS_UNITS') ||
461 									' (dcm_product_id ,display_units_id) ' ||
462 									' (select :1, :2 from dual)';
463 				msd_dem_common_utilities.log_debug(l_stmt);
464 				execute immediate l_stmt using l_component_id_sop,new_uom.display_units_id;
465                 -- syenamar
466 
467 		else
468 				l_stmt := null;
469 				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)' ||
470 				          ', column_name =  (select data_field from ' || get_lookup_value('MSD_DEM_TABLES', 'DISPLAY_UNITS') || ' where display_units = :2)' ||
471 				          'where ebs_entity = ''UOM'' and demantra_entity = ''DISPLAY_UNIT'' and internal_name = :3';
472 
473 				msd_dem_common_utilities.log_debug(l_stmt);
474 
475 				execute immediate l_stmt using p_uom_code, p_uom_code, p_uom_code;
476 
477 		end if;
478 
479 		exception
480 			when others then
481 				msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
482 				msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
483 				retcode := 1;
484 
485 end;
486 
487 procedure populate_demantra_uom_table(errbuf                 out nocopy varchar2,
488 																		  retcode                out nocopy number,
489 																			p_instance_id          in  number,
490 																			p_include_uom_list     in varchar2,
491 																			p_exclude_uom_list     in varchar2)
492 
493 as
494 
495 type c_uom_code is ref cursor;
496 
497 get_uom_code c_uom_code;
498 
499 l_stmt varchar2(500);
500 l_list varchar2(500);
501 l_list2 varchar2(500);
502 
503 l_uom_code varchar2(30);
504 
505 begin
506 
507 
508 		l_stmt := null;
509 		l_stmt := 'select distinct from_uom_code uom_code
510 							 from msd_dem_uom_conversions_gtt
511 							 union
512 							 select distinct to_uom_code uom_code
513 							 from msd_dem_uom_conversions_gtt';
514 
515 		l_list := null;
516 		l_list2 := null;
517 
518 		if p_include_uom_list is not null then
519 
520 			l_list := '''' || replace(p_include_uom_list, ',', ''',''') || '''';
521 			l_list2 := p_include_uom_list;
522 
523 		elsif p_exclude_uom_list is not null then
524 
525 			l_list := '''' || replace(p_exclude_uom_list, ',', ''',''') || '''';
526 
527 		end if;
528 
529 		l_list2 := l_list2 || ',';
530 
531 		if l_list is not null then
532 
533 			l_stmt := 'select uom_code from (' || l_stmt;
534 			l_stmt := l_stmt || ')' || 'where uom_code ';
535 
536 			if p_include_uom_list is null then
537 				l_stmt := l_stmt || ' not ';
538 			end if;
539 
540 			l_stmt := l_stmt || ' in ' || '(' || l_list || ')';
541 
542 		end if;
543 
544 
545 
546 		open get_uom_code for l_stmt;
547 
548 		loop
549 
550 		fetch get_uom_code into l_uom_code;
551 		exit when get_uom_code%notfound;
552 
553 		msd_dem_common_utilities.log_message('Populating UOM: ' || l_uom_code);
554 		msd_dem_common_utilities.log_debug('Populating UOM: ' || l_uom_code);
555 
556 		l_list2 := replace(l_list2, l_uom_code||',' , '');
557 
558 		process_uom(retcode, l_uom_code);
559 
560 		if retcode = -1 or retcode = 1 then
561 			msd_dem_common_utilities.log_message('Failed processing UOM: ' || l_uom_code);
562 			msd_dem_common_utilities.log_debug('Failed Processing UOM: ' || l_uom_code);
563 			goto continue;
564 		end if;
565 
566 		populate_uom(retcode, l_uom_code, p_instance_id);
567 
568 <<continue>>
569 		null;
570 
571 		end loop;
572 
573 		close get_uom_code;
574 
575 		l_list2 := rtrim(l_list2, ',');
576 
577 		if l_list2 is not null then
578 		  retcode := 1;
579 			msd_dem_common_utilities.log_message('Following UOM''s dont exist in source: ' || l_list2);
580 			msd_dem_common_utilities.log_debug('Following UOM''s dont exist in source: ' || l_list2);
581 		end if;
582 
583 		exception
584 			when others then
585 				msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
586 				msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
587 				retcode := 1;
588 
589 end;
590 
591 procedure collect_from_source(errbuf        out nocopy varchar2,
592 											        retcode       out nocopy number,
593 											        p_instance_id in  number)
594 
595 as
596 
597 l_stmt varchar2(4000);
598 l_key_values varchar2(4000);
599 l_instance_type number;
600 l_retcode number;
601 x_inst_type_sql varchar2(200);
602 
603 begin
604 
605 		-- Bug#8367471 nallkuma
606 		l_instance_type := null;
607 
608 		x_inst_type_sql := ' select instance_type from msc_apps_instances where instance_id = ' || p_instance_id ;
609 		execute immediate x_inst_type_sql into l_instance_type ;
610 		msd_dem_common_utilities.log_debug('Instance_type : ' || l_instance_type);
611 
612 		if l_instance_type in (1,2,4) then /* Non-Legacy Instances */ -- Bug#8367471 nallkuma
613 		  l_instance_type := ' 4 ' ;
614 		else                               /* Legacy Instances    */
615 		  l_instance_type := ' 3 ' ;
616     end if;
617 
618 
619 	  l_key_values := '$C_INSTANCE#' || p_instance_id ||
620 										'$C_INST_TYPE#' || l_instance_type || '$'; -- Bug#8367471 nallkuma
621 
622 	  msd_dem_query_utilities.get_query2 (
623              			l_retcode,
624              			l_stmt,
625              			'MSD_DEM_UOM_FROM_SOURCE',
626              			p_instance_id,
627              			l_key_values,
628              			0,
629              			null);
630 
631 		if l_stmt  is null then
632 
633 			msd_dem_common_utilities.log_message('Cannot find query to get UOM conversions from source');
634 			msd_dem_common_utilities.log_debug('Cannot find query to get UOM conversions from source');
635 			retcode := -1;
636 			return;
637 
638 		end if;
639 
640 		msd_dem_common_utilities.log_debug('Query: ' || l_stmt);
641 
642 		msd_dem_common_utilities.log_debug('p_instance_id ' || p_instance_id);
643 
644 		msd_dem_common_utilities.log_debug('Start Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
645 		execute immediate l_stmt;
646 		msd_dem_common_utilities.log_debug('End Time: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
647 
648 		exception
649 			when others then
650 				errbuf  := substr(SQLERRM,1,150);
651 				msd_dem_common_utilities.log_message(errbuf);
652 				msd_dem_common_utilities.log_debug(errbuf);
653 				retcode := -1;
654 
655 
656 end;
657 
658 procedure collect_uom(errbuf                 out nocopy varchar2,
659 											retcode                out nocopy number,
660 											p_instance_id          in  number,
661 											p_include_all          in  number,
662 											p_include_uom_list     in varchar2,
663 											p_exclude_uom_list     in varchar2)
664 
665 as
666 
667 begin
668 
669 		if p_include_all = 1 and (p_include_uom_list is not null or p_exclude_uom_list is not null) then
670 			msd_dem_common_utilities.log_message('Cannot specify both collect all and include or exclude list');
671 			msd_dem_common_utilities.log_debug('Cannot specify both collect all and include or exclude list');
672 			retcode := -1;
673 			return;
674 		end if;
675 
676 		if p_include_all = 2 and p_include_uom_list is null and p_exclude_uom_list is null then
677 
678 			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');
679 			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');
680 			retcode := -1;
681 			return;
682 
683 		end if;
684 
685 		if p_include_all = 2 and p_include_uom_list is not null and p_exclude_uom_list is not null then
686 
687 			msd_dem_common_utilities.log_message('Should not specify both include and exclude list');
688 			msd_dem_common_utilities.log_debug('Should not specify both include and exclude list');
689 			retcode := -1;
690 			return;
691 
692 		end if;
693 
694 		msd_dem_common_utilities.log_message('Collecting UOM''s');
695 		msd_dem_common_utilities.log_debug('Collecting UOM''s');
696 
697 		msd_dem_common_utilities.log_message('Collecting UOM conversions from source');
698 		msd_dem_common_utilities.log_debug('Collecting UOM conversions from source');
699 		collect_from_source(errbuf, retcode, p_instance_id);
700 
701 		if retcode = -1 then
702 			goto error_handle;
703 		end if;
704 
705 
706 		msd_dem_common_utilities.log_message('Populating UOM''s in Demand Planning components');
707 		msd_dem_common_utilities.log_debug('Populating UOM''s in Demand Planning components');
708 		populate_demantra_uom_table(errbuf, retcode, p_instance_id, p_include_uom_list, p_exclude_uom_list);
709 
710 		commit;
711 
712 		if retcode <> -1 and retcode <> 1 then
713 			retcode := 0;
714 		end if;
715 		return;
716 
717 <<error_handle>>
718 		retcode := -1;
719 
720 		exception
721 			when others then
722 				errbuf  := substr(SQLERRM,1,150);
723 				msd_dem_common_utilities.log_message(errbuf);
724 				msd_dem_common_utilities.log_debug(errbuf);
725 				retcode := -1;
726 
727 
728 end;
729 
730 END MSD_DEM_COLLECT_UOMS;
731