1 PACKAGE BODY MSD_DEM_COMMON_UTILITIES AS
2 /* $Header: msddemcub.pls 120.11 2008/01/03 07:19:35 nallkuma noship $ */
3
4 /*** PRIVATE PROCEDURES ***
5 * MSD_UOM_CONVERSION
6 */
7
8 PROCEDURE msd_uom_conversion (from_unit varchar2,
9 to_unit varchar2,
10 item_id number,
11 uom_rate OUT NOCOPY number ) IS
12
13 from_class varchar2(10);
14 to_class varchar2(10);
15
16 CURSOR standard_conversions IS
17 select t.conversion_rate std_to_rate,
18 t.uom_class std_to_class,
19 f.conversion_rate std_from_rate,
20 f.uom_class std_from_class
21 from msc_uom_conversions t,
22 msc_uom_conversions f
23 where t.inventory_item_id in (item_id, 0) and
24 t.uom_code = to_unit and
25 nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
26 f.inventory_item_id in (item_id, 0) and
27 f.uom_code = from_unit and
28 nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
29 order by t.inventory_item_id desc, f.inventory_item_id desc;
30
31
32 std_rec standard_conversions%rowtype;
33
34
35 CURSOR interclass_conversions(p_from_class VARCHAR2, p_to_class VARCHAR2) IS
36 select decode(from_uom_class, p_from_class, 1, 2) from_flag,
37 decode(to_uom_class, p_to_class, 1, 2) to_flag,
38 conversion_rate rate
39 from msc_uom_class_conversions
40 where inventory_item_id = item_id and
41 nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) and
42 ( (from_uom_class = p_from_class and to_uom_class = p_to_class) or
43 (from_uom_class = p_to_class and to_uom_class = p_from_class) );
44
45 class_rec interclass_conversions%rowtype;
46
47 invalid_conversion exception;
48
49 type conv_tab is table of number index by binary_integer;
50 type class_tab is table of varchar2(10) index by binary_integer;
51
52 interclass_rate_tab conv_tab;
53 from_class_flag_tab conv_tab;
54 to_class_flag_tab conv_tab;
55 from_rate_tab conv_tab;
56 to_rate_tab conv_tab;
57 from_class_tab class_tab;
58 to_class_tab class_tab;
59
60 std_index number;
61 class_index number;
62
63 from_rate number := 1;
64 to_rate number := 1;
65 interclass_rate number := 1;
66 to_class_rate number := 1;
67 from_class_rate number := 1;
68 msgbuf varchar2(500);
69
70 begin
71
72 /*
73 ** Conversion between between two UOMS.
74 **
75 ** 1. The conversion always starts from the conversion defined, if exists,
76 ** for an specified item.
77 ** 2. If the conversion id not defined for that specific item, then the
78 ** standard conversion, which is defined for all items, is used.
79 ** 3. When the conversion involves two different classes, then
80 ** interclass conversion is activated.
81 */
82
83 /* If from and to units are the same, conversion rate is 1.
84 Go immediately to the end of the procedure to exit.*/
85
86 if (from_unit = to_unit) then
87 uom_rate := 1;
88 goto procedure_end;
89 end if;
90
91
92 /* Get item specific or standard conversions */
93 open standard_conversions;
94 std_index := 0;
95 loop
96
97 std_index := std_index + 1;
98
99 fetch standard_conversions into std_rec;
100 exit when standard_conversions%notfound;
101
102 from_rate_tab(std_index) := std_rec.std_from_rate;
103 from_class_tab(std_index) := std_rec.std_from_class;
104 to_rate_tab(std_index) := std_rec.std_to_rate;
105 to_class_tab(std_index) := std_rec.std_to_class;
106
107 end loop;
108
109 close standard_conversions;
110
111 if (std_index = 0) then /* No conversions defined */
112 msgbuf := msgbuf||'Invalid standard conversion : ';
113 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
114 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
115 raise invalid_conversion;
116
117 else
118 /* Conversions are ordered.
119 Item specific conversions will be returned first. */
120
121 from_class := from_class_tab(1);
122 to_class := to_class_tab(1);
123 from_rate := from_rate_tab(1);
124 to_rate := to_rate_tab(1);
125
126 end if;
127
128
129 /* Load interclass conversion tables */
130 if (from_class <> to_class) then
131 class_index := 0;
132 open interclass_conversions (from_class, to_class);
133 loop
134
135 fetch interclass_conversions into class_rec;
136 exit when interclass_conversions%notfound;
137
138 class_index := class_index + 1;
139
140 to_class_flag_tab(class_index) := class_rec.to_flag;
141 from_class_flag_tab(class_index) := class_rec.from_flag;
142 interclass_rate_tab(class_index) := class_rec.rate;
143
144 end loop;
145 close interclass_conversions;
146
147 /* No interclass conversion is defined */
148 if (class_index = 0 ) then
149 msgbuf := msgbuf||'Invalid Interclass conversion : ';
150 msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
151 msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
152 raise invalid_conversion;
153 else
154 if ( to_class_flag_tab(1) = 1 and from_class_flag_tab(1) = 1 ) then
155 to_class_rate := interclass_rate_tab(1);
156 from_class_rate := 1;
157 else
158 from_class_rate := interclass_rate_tab(1);
159 to_class_rate := 1;
160 end if;
161 interclass_rate := from_class_rate/to_class_rate;
162 end if;
163 end if; /* End of from_class <> to_class */
164
165 /*
166 ** conversion rates are defaulted to '1' at the start of the procedure
167 ** so seperate calculations are not required for standard/interclass
168 ** conversions
169 */
170
171 if (to_rate <> 0 ) then
172 uom_rate := (from_rate * interclass_rate) / to_rate;
173 else
174 uom_rate := 1;
175 end if;
176
177
178 /* Put a label and a null statement over here so that you can
179 the goto statements can branch here */
180 <<procedure_end>>
181
182 null;
183
184 exception
185
186 when others then
187 uom_rate := 1;
188
189 END msd_uom_conversion;
190
191
192 /*** PUBLIC PROCEDURES ***
193 * LOG_MESSAGE
194 * LOG_DEBUG
195 * GET_DBLINK
196 * GET_INSTANCE_INFO
197 */
198
199
200 /*
201 * This procedure logs a given message text in the concurrent request log file.
202 * param: p_buff - message text to be logged.
203 */
204 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2)
205 IS
206 BEGIN
207 fnd_file.put_line (fnd_file.log, p_buff);
208 END LOG_MESSAGE;
209
210
211 /*
212 * This procedure logs a given debug message text in the concurrent request log file
213 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
214 * param: p_buff - debug message text to be logged.
215 */
216 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2)
217 IS
218 BEGIN
219 IF (C_MSD_DEM_DEBUG = 'Y') THEN
220 fnd_file.put_line (fnd_file.output, p_buff);
221 END IF;
222 END LOG_DEBUG;
223
224
225 /*
226 * This procedure gets the db link to the given source instance
227 */
228 PROCEDURE GET_DBLINK (
229 errbuf OUT NOCOPY VARCHAR2,
230 retcode OUT NOCOPY VARCHAR2,
231 p_sr_instance_id IN NUMBER,
232 p_dblink OUT NOCOPY VARCHAR2)
233 IS
234 BEGIN
235 SELECT decode ( m2a_dblink, null, '', '@' || m2a_dblink)
236 INTO p_dblink
237 FROM msc_apps_instances
238 WHERE instance_id = p_sr_instance_id;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 retcode := -1 ;
243 errbuf := substr(SQLERRM,1,150);
244 RETURN;
245 END GET_DBLINK;
246
247
248 /*
249 * This procedure gets the instance info given the source instance id
250 */
251 PROCEDURE GET_INSTANCE_INFO (
252 errbuf OUT NOCOPY VARCHAR2,
253 retcode OUT NOCOPY VARCHAR2,
254 p_instance_code OUT NOCOPY VARCHAR2,
255 p_apps_ver OUT NOCOPY NUMBER,
256 p_dgmt OUT NOCOPY NUMBER,
257 p_instance_type OUT NOCOPY NUMBER,
258 p_sr_instance_id IN NUMBER)
259 IS
260 BEGIN
261
262 SELECT
263 instance_code,
264 apps_ver,
265 gmt_difference/24.0,
266 instance_type
267 INTO
268 p_instance_code,
269 p_apps_ver,
270 p_dgmt,
271 p_instance_type
272 FROM msc_apps_instances
273 WHERE instance_id= p_sr_instance_id;
274 EXCEPTION
275 WHEN OTHERS THEN
276 retcode := -1 ;
277 errbuf := substr(SQLERRM,1,150);
278 RETURN;
279 END GET_INSTANCE_INFO;
280
281
282 /*
283 * This procedure will refresh Purge Series Data data profile to its defualt value
284 * i.e. it will set the data profile option to No Load and No Purge for all series
285 * included in the profile.
286 */
287
288 PROCEDURE REFRESH_PURGE_SERIES (
289 errbuf OUT NOCOPY VARCHAR2,
290 retcode OUT NOCOPY VARCHAR2,
291 p_profile_id IN NUMBER,
292 p_schema IN VARCHAR2)
293 IS
294
295 TYPE REF_CURSOR_TYPE IS REF CURSOR;
296
297 c_ref_cursor REF_CURSOR_TYPE;
298
299 x_sql varchar2(500);
300 l_sql varchar2(500);
301 x_series_id number;
302
303 BEGIN
304
305 x_sql := 'select series_id from ' ||p_schema ||'.transfer_query_series where id = ' || p_profile_id;
306
307 OPEN c_ref_cursor FOR x_sql;
308
309 LOOP
310 FETCH c_ref_cursor INTO x_series_id;
311 EXIT WHEN c_ref_cursor%NOTFOUND;
312
313 l_sql := 'begin ' || p_schema|| '.API_MODIFY_INTEG_SERIES_ATTR('||p_profile_id||', '|| x_series_id||', 2, 0); end;';
314 execute immediate l_sql;
315 end loop;
316
317 close c_ref_cursor;
318
319 END;
320
321 /*
322 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
323 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
324 * if Demantra is installed.
325 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
326 * The checks if the table MDP_MATRIX exists in the Demantra Schema
327 */
328
329 PROCEDURE UPDATE_SYNONYMS (
330 errbuf OUT NOCOPY VARCHAR2,
331 retcode OUT NOCOPY VARCHAR2)
332
333 IS
334
335 CURSOR c_get_dm_schema
336 IS
337 SELECT owner
338 FROM dba_objects
339 WHERE owner = owner
340 AND object_type = 'TABLE'
341 AND object_name = 'MDP_MATRIX'
342 ORDER BY created desc;
343
344 x_dem_schema VARCHAR2(50) := NULL;
345 x_create_synonym_sql VARCHAR2(200) := NULL;
346
347 x_grant_execute_sql VARCHAR2(200) := NULL;
348
349 x_curr_val VARCHAR2(50) := NULL;
350
351 x_success BOOLEAN := NULL;
352
353
354 BEGIN
355
356 OPEN c_get_dm_schema;
357 FETCH c_get_dm_schema INTO x_dem_schema;
358 CLOSE c_get_dm_schema;
359
360 /* Demantra is Installed */
361 IF (x_dem_schema IS NOT NULL)
362 THEN
363 /* Update synonym MSD_DEM_TRANSFER_LIST to point to Demantra table TRANSFER_LIST */
364 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_LIST FOR ' ||
365 x_dem_schema || '.TRANSFER_LIST';
366
367 EXECUTE IMMEDIATE x_create_synonym_sql;
368
369 /* Update synonym MSD_DEM_TRANSFER_QUERY to point to Demantra table TRANSFER_QUERY */
370 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY FOR ' ||
371 x_dem_schema || '.TRANSFER_QUERY';
372
373 EXECUTE IMMEDIATE x_create_synonym_sql;
374
375 /* Update synonym MSD_DEM_TRANSFER_QUERY_LEVELS to point to Demantra table TRANSFER_QUERY_LEVELS */
376 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_TRANSFER_QUERY_LEVELS FOR ' ||
377 x_dem_schema || '.TRANSFER_QUERY_LEVELS';
378
379 EXECUTE IMMEDIATE x_create_synonym_sql;
380
381 /* Update synonym MSD_DEM_GROUP_TABLES to point to Demantra table GROUP_TABLES */
382 x_create_synonym_sql := 'CREATE OR REPLACE SYNONYM MSD_DEM_GROUP_TABLES FOR ' ||
383 x_dem_schema || '.GROUP_TABLES';
384
385 EXECUTE IMMEDIATE x_create_synonym_sql;
386
387 /* Grant execute permissions to Demantra Schema on pakcage MSD_DEM_UPLOAD_FORECAST */
388 x_grant_execute_sql := 'GRANT EXECUTE ON MSD_DEM_UPLOAD_FORECAST TO ' || x_dem_schema;
389 EXECUTE IMMEDIATE x_grant_execute_sql;
390
391 /* Set the profile MSD_DEM_SCHEMA if not set */
392 x_curr_val := fnd_profile.value('MSD_DEM_SCHEMA');
393
394 IF (nvl(x_curr_val, '$$$') <> x_dem_schema)
395 THEN
396 x_success := fnd_profile.save ('MSD_DEM_SCHEMA', x_dem_schema, 'SITE');
397 END IF;
398
399 END IF;
400 commit;
401 EXCEPTION
402 WHEN OTHERS THEN
403 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
404 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
405 retcode := -1;
406 END;
407
408 /* Deletes the msd_dem_entities_inuse table if the new demantra schema is intstalled
409 * this will ensure that there will be no mapping between the seeded units in APPS and
410 * the (display uints,exchange rate,indexes) in Demantra */
411 PROCEDURE cleanup_entities_inuse(errbuf out nocopy varchar2, retcode out nocopy varchar2)
412 as
413 /*Deletes the msd_dem_entities_inuse table */
414 BEGIN
415
416 delete msd_dem_entities_inuse;
417 commit;
418 EXCEPTION
419 when others then
420 msd_dem_common_utilities.log_message(substr(SQLERRM,1,150));
421 msd_dem_common_utilities.log_debug(substr(SQLERRM,1,150));
422 retcode := -1;
423 END;
424
425
426
427 /*** PUBLIC FUNCTIONS ***
428 * GET_ALL_ORGS
429 * DM_TIME_LEVEL
430 * GET_PARAMETER_VALUE
431 * GET_LOOKUP_VALUE
432 * GET_UOM_CODE
433 * GET_SR_INSTANCE_ID_FOR_ZONE
434 * UOM_CONVERT
435 * IS_PF_FCSTABLE_FOR_ITEM
436 * IS_PRODUCT_FAMILY_FORECASTABLE
437 * GET_SUPPLIER_CALENDAR
438 * GET_SAFETY_STOCK_ENDDATE
439 * GET_PERIOD_DATE_FOR_DUMMY
440 * GET_SITE_FOR_CSF
441 * IS_LAST_DATE_IN_BUCKET
442 * GET_SNO_PLAN_CUTOFF_DATE
443 * IS_SUPPLIER_CALENDAR_PRESENT
444 */
445
446 /*
447 * This function returns the comma(,) separated list of demand management enabled orgs
448 * belonging to the given org group.
449 */
450 FUNCTION GET_ALL_ORGS (
451 p_org_group IN VARCHAR2,
452 p_sr_instance_id IN NUMBER)
453 RETURN VARCHAR2
454 IS
455
456 TYPE REF_CURSOR_TYPE IS REF CURSOR;
457
458 c_ref_cursor REF_CURSOR_TYPE;
459
460 x_errbuf VARCHAR2(200) := NULL;
461 x_retcode VARCHAR2(100) := NULL;
462
463 x_dblink VARCHAR2(50) := NULL;
464 x_sql VARCHAR2(1000) := NULL;
465 x_org VARCHAR2(10) := NULL;
466 x_org_string VARCHAR2(1000) := NULL;
467
468 BEGIN
469
470 /* Get the db link to the source instance */
471 msd_dem_common_utilities.get_dblink (
472 x_errbuf,
473 x_retcode,
474 p_sr_instance_id,
475 x_dblink);
476
477 IF (x_retcode = '-1')
478 THEN
479 RETURN NULL;
480 END IF;
481
482 x_sql := 'SELECT mp.organization_code org_code ' ||
483 ' FROM msc_instance_orgs mio, mtl_parameters' || x_dblink || ' mp ' ||
484 ' WHERE mio.organization_id = mp.organization_id ' ||
485 ' AND mio.sr_instance_id = :b_sr_instance_id ' ||
486 ' AND mio.org_group = :b_org_group ' ||
487 ' AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ';
488
489 OPEN c_ref_cursor FOR x_sql USING p_sr_instance_id, p_org_group;
490
491 LOOP
492
493 FETCH c_ref_cursor INTO x_org;
494 EXIT WHEN c_ref_cursor%NOTFOUND;
495
496 IF (c_ref_cursor%ROWCOUNT = 1)
497 THEN
498 x_org_string := x_org;
499 ELSE
500 x_org_string := x_org_string || ',' || x_org;
501 END IF;
502
503 END LOOP;
504
505 CLOSE c_ref_cursor;
506
507 RETURN x_org_string;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 RETURN NULL;
512
513 END GET_ALL_ORGS;
514
515
516 /* This function returns the Active Demantra Data Model time level (Day/Month/week) */
517
518 FUNCTION DM_TIME_LEVEL RETURN VARCHAR2 IS
519
520 CURSOR C1 IS
521 select MEANING
522 from fnd_lookup_values_vl
523 where lookup_type = 'MSD_DEM_TABLES'
524 AND LOOKUP_CODE = 'DM_WIZ_DM_DEF';
525
526
527
528 L_STMT VARCHAR2(10000);
529
530 L_DM VARCHAR2(240);
531
532 L_TIM_LEVEL VARCHAR2(240);
533
534
535 BEGIN
536
537 /*
538 OPEN C1;
539 FETCH C1 INTO L_DM;
540 CLOSE C1;
541 */
542
543 L_DM := msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'DM_WIZ_DM_DEF');
544
545 L_STMT := 'SELECT TIME_BUCKET FROM '||
546 L_DM||
547 ' WHERE IS_ACTIVE=1 ';
548
549 EXECUTE IMMEDIATE L_STMT INTO L_TIM_LEVEL;
550
551 RETURN L_TIM_LEVEL;
552
553 END DM_TIME_LEVEL;
554
555
556
557 /*
558 * This function returns the parameter_value in msd_dem_setup_parameters
559 * given the parameter_name
560 */
561 FUNCTION GET_PARAMETER_VALUE (
562 p_sr_instance_id NUMBER,
563 p_parameter_name VARCHAR2)
564 RETURN VARCHAR2
565 IS
566 x_errbuf VARCHAR2(200) := NULL;
567 x_retcode VARCHAR2(100) := NULL;
568
569 x_dblink VARCHAR2(50) := NULL;
570 x_parameter_value VARCHAR2(255) := NULL;
571
572 x_sr_category_set_id NUMBER := NULL;
573
574 BEGIN
575
576 get_dblink (
577 x_errbuf,
578 x_retcode,
579 p_sr_instance_id,
580 x_dblink);
581
582 IF (x_retcode = -1)
583 THEN
584 RETURN NULL;
585 END IF;
586
587 EXECUTE IMMEDIATE 'SELECT parameter_value FROM msd_dem_setup_parameters' || x_dblink ||
588 ' WHERE parameter_name = ''' || p_parameter_name || ''''
589 INTO x_parameter_value;
590
591 /* Get the destination category set id for parameter = MSD_DEM_CATEGORY_SET_NAME */
592 IF (p_parameter_name = 'MSD_DEM_CATEGORY_SET_NAME')
593 THEN
594 x_sr_category_set_id := to_number(x_parameter_value);
595
596 SELECT category_set_id
597 INTO x_parameter_value
598 FROM msc_category_set_id_lid
599 WHERE sr_instance_id = p_sr_instance_id
600 AND sr_category_set_id = x_sr_category_set_id;
601
602 END IF;
603
604 RETURN x_parameter_value;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 RETURN NULL;
609
610 END GET_PARAMETER_VALUE;
611
612
613
614 /*
615 * This function returns the lookup_value given the lookup_type
616 * and lookup_code
617 */
618 function get_lookup_value(p_lookup_type IN VARCHAR2,
619 p_lookup_code IN VARCHAR2)
620 return VARCHAR2
621
622 as
623
624 cursor get_lookup_value is
625 select meaning
626 from fnd_lookup_values
627 where lookup_type = p_lookup_type
628 and lookup_code = p_lookup_code
629 and language = 'US';
630
631 cursor get_schema_name is
632 select fnd_profile.value('MSD_DEM_SCHEMA')
633 from dual;
634
635 CURSOR c_is_mdp_matrix_present (p_schema_name VARCHAR2)
636 IS
637 SELECT table_name
638 FROM all_tables
639 WHERE owner = upper(p_schema_name)
640 AND table_name = 'MDP_MATRIX';
641
642 l_lookup_value varchar2(200);
643 l_schema_name varchar2(200);
644
645 x_retval BOOLEAN := NULL;
646 x_table_name VARCHAR2(50) := NULL;
647 x_msd_schema_name VARCHAR2(50) := NULL;
648 x_dummy1 VARCHAR2(50) := NULL;
649 x_dummy2 VARCHAR2(50) := NULL;
650
651 begin
652
653 open get_lookup_value;
654 fetch get_lookup_value into l_lookup_value;
655 close get_lookup_value;
656
657 if p_lookup_type = 'MSD_DEM_TABLES' then
658
659 open get_schema_name;
660 fetch get_schema_name into l_schema_name;
661 close get_schema_name;
662
663 if l_schema_name is not null then
664 l_lookup_value := l_schema_name || '.' || l_lookup_value;
665 else
666 return null;
667 end if;
668
669 end if;
670
671 IF (p_lookup_type = 'MSD_DEM_DM_STAGING_TABLES')
672 THEN
673
674 open get_schema_name;
675 fetch get_schema_name into l_schema_name;
676 close get_schema_name;
677
678 IF (l_schema_name IS NULL)
679 THEN
680 RETURN NULL;
681 END IF;
682
683 OPEN c_is_mdp_matrix_present (l_schema_name);
684 FETCH c_is_mdp_matrix_present INTO x_table_name;
685 CLOSE c_is_mdp_matrix_present;
686
687 IF (x_table_name IS NOT NULL)
688 THEN
689 l_lookup_value := l_schema_name || '.' || l_lookup_value;
690 ELSE
691 x_retval := fnd_installation.get_app_info (
692 'MSD',
693 x_dummy1,
694 x_dummy2,
695 x_msd_schema_name);
696
697 l_lookup_value := x_msd_schema_name || '.' || l_lookup_value;
698 END IF;
699
700 END IF;
701
702 return l_lookup_value;
703
704 end;
705
706
707 /*
708 * This function returns the UOM code given the display unit id
709 */
710 FUNCTION GET_UOM_CODE (
711 p_unit_id IN NUMBER)
712 RETURN VARCHAR2
713 IS
714 x_uom_code VARCHAR2(100) := NULL;
715 BEGIN
716 EXECUTE IMMEDIATE 'SELECT display_units FROM ' ||
717 get_lookup_value ('MSD_DEM_TABLES', 'DISPLAY_UNITS') ||
718 ' WHERE display_units_id = ' || p_unit_id
719 INTO x_uom_code;
720 RETURN x_uom_code;
721 EXCEPTION
722 WHEN OTHERS THEN
723 RETURN NULL;
724 END GET_UOM_CODE;
725
726
727 /*
728 * This function returns a sr_instance_id in which the zone is defined
729 */
730 FUNCTION GET_SR_INSTANCE_ID_FOR_ZONE (
731 p_zone IN VARCHAR2)
732 RETURN NUMBER
733 IS
734 x_sr_instance_id NUMBER := NULL;
735 BEGIN
736 SELECT sr_instance_id
737 INTO x_sr_instance_id
738 FROM msc_regions
739 WHERE zone = p_zone
740 AND rownum < 2;
741
742 RETURN x_sr_instance_id;
743 EXCEPTION
744 WHEN OTHERS THEN
745 RETURN NULL;
746 END GET_SR_INSTANCE_ID_FOR_ZONE;
747
748
749 /*
750 * This function returns the conversion rate for the given item, From UOM and To UOM
751 */
752 FUNCTION UOM_CONVERT (
753 p_inventory_item_id IN NUMBER,
754 p_precision IN NUMBER,
755 p_from_unit IN VARCHAR2,
756 p_to_unit IN VARCHAR2)
757 RETURN NUMBER
758 IS
759
760 x_uom_rate NUMBER := NULL;
761
762 BEGIN
763
764 IF ( p_from_unit IS NULL
765 OR p_to_unit IS NULL)
766 THEN
767 RETURN 1;
768 END IF;
769
770 msd_uom_conversion (
771 p_from_unit,
772 p_to_unit,
773 p_inventory_item_id,
774 x_uom_rate);
775
776 IF (x_uom_rate = -99999)
777 THEN
778 RETURN 1;
779 END IF;
780
781 IF (p_precision IS NULL)
782 THEN
783 RETURN x_uom_rate;
784 ELSE
785 RETURN round (x_uom_rate, p_precision);
786 END IF;
787
788 EXCEPTION
789 WHEN OTHERS THEN
790 RETURN 1;
791 END UOM_CONVERT;
792
793
794
795 /* This function returns 1 if the product family's forecast control is set
796 * for the given item in the master org, else returns 2
797 */
798 FUNCTION IS_PF_FCSTABLE_FOR_ITEM (
799 p_sr_inventory_item_id IN NUMBER,
800 p_sr_instance_id IN NUMBER,
801 p_master_org_id IN NUMBER)
802 RETURN NUMBER
803 IS
804
805 x_product_family_id NUMBER := NULL;
806 x_is_fcstable NUMBER := NULL;
807
808 BEGIN
809
810 /* First get the product family id */
811 SELECT msi.product_family_id
812 INTO x_product_family_id
813 FROM msc_system_items msi
814 WHERE
815 msi.plan_id = -1
816 AND msi.sr_instance_id = p_sr_instance_id
817 AND msi.organization_id = p_master_org_id
818 AND msi.sr_inventory_item_id = p_sr_inventory_item_id;
819
820 IF (x_product_family_id IS NULL)
821 THEN
822 RETURN 2;
823 END IF;
824
825 SELECT nvl(msi.ato_forecast_control, 3)
826 INTO x_is_fcstable
827 FROM msc_system_items msi
828 WHERE msi.plan_id = -1
829 AND msi.sr_instance_id = p_sr_instance_id
830 AND msi.organization_id = p_master_org_id
831 AND msi.inventory_item_id = x_product_family_id;
832
833 IF (x_is_fcstable = 3)
834 THEN
835 RETURN 2;
836 END IF;
837
838 RETURN 1;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 RETURN 2;
843 END IS_PF_FCSTABLE_FOR_ITEM;
844
845
846
847 /* This function returns 1 if the product family forecast control flag is set,
848 * else returns 2
849 */
850 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (
851 p_inventory_item_id IN NUMBER,
852 p_sr_instance_id IN NUMBER)
853 RETURN NUMBER
854 IS
855
856 x_errbuf VARCHAR2(200) := NULL;
857 x_retcode VARCHAR2(100) := NULL;
858
859 x_dblink VARCHAR2(50) := NULL;
860 x_sql VARCHAR2(255) := NULL;
861
862 x_return_value NUMBER := NULL;
863 x_is_fcstable NUMBER := NULL;
864
865 BEGIN
866
867 get_dblink (
868 x_errbuf,
869 x_retcode,
870 p_sr_instance_id,
871 x_dblink);
872
873 IF (x_retcode = -1)
874 THEN
875 RETURN 2;
876 END IF;
877
878 x_sql := 'BEGIN :x_ou1 := MSD_DEM_SR_UTIL.GET_MASTER_ORGANIZATION' || x_dblink || '; END;';
879 EXECUTE IMMEDIATE x_sql USING OUT x_return_value;
880
881 SELECT nvl(msi.ato_forecast_control, 3)
882 INTO x_is_fcstable
883 FROM msc_system_items msi
884 WHERE msi.plan_id = -1
885 AND msi.sr_instance_id = p_sr_instance_id
886 AND msi.organization_id = x_return_value
887 AND msi.inventory_item_id = p_inventory_item_id;
888
889 IF (x_is_fcstable = 3)
890 THEN
891 RETURN 2;
892 END IF;
893
894 RETURN 1;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898 RETURN 2;
899 END IS_PRODUCT_FAMILY_FORECASTABLE;
900
901
902 /*
903 * This function gets the calendar code
904 */
905 FUNCTION GET_SUPPLIER_CALENDAR (
906 p_plan_id IN NUMBER,
907 p_sr_instance_id IN NUMBER,
908 p_organization_id IN NUMBER,
909 p_inventory_item_id IN NUMBER,
910 p_supplier_id IN NUMBER,
911 p_supplier_site_id IN NUMBER,
912 p_using_organization_id IN NUMBER)
913 RETURN VARCHAR2
914 IS
915
916 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number, p_inventory_item_id IN NUMBER,
917 p_supplier_id in number, p_supplier_site_id in number, p_using_organization_id in number) IS
918 select DELIVERY_CALENDAR_CODE
919 from msc_item_suppliers
920 where plan_id = p_plan_id
921 and sr_instance_id = p_sr_instance_id
922 and organization_id = p_organization_id
923 and inventory_item_id = p_inventory_item_id
924 and supplier_id = p_supplier_id
925 and supplier_site_id = p_supplier_site_id
926 and using_organization_id = p_using_organization_id;
927
928 cursor c2 (p_sr_instance_id IN NUMBER, p_organization_id IN number) IS
929 select calendar_code
930 from msc_trading_partners
931 where partner_type = 3
932 and sr_tp_id = p_organization_id
933 and sr_instance_id = p_sr_instance_id;
934
935 l_ret varchar2(30) := null;
936 Begin
937
938 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id,
939 p_supplier_id, p_supplier_site_id, p_using_organization_id);
940 fetch c1 into l_ret;
941 close c1;
942
943 if l_ret is null then
944 open c2 (p_sr_instance_id, p_organization_id);
945 fetch c2 into l_ret;
946 close c2;
947 end if;
948
949 return l_ret;
950 EXCEPTION when others then return NULL;
951
952 End get_supplier_calendar;
953
954 /*
955 * This function gets the period end date
956 */
957 FUNCTION GET_SAFETY_STOCK_ENDDATE (
958 p_plan_id IN NUMBER,
959 p_sr_instance_id IN NUMBER,
960 p_organization_id IN NUMBER,
961 p_inventory_item_id IN NUMBER,
962 p_period_start_date IN DATE)
963 RETURN DATE
964 IS
965 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number,
966 p_inventory_item_id IN NUMBER, p_period_start_date IN DATE) IS
967 select min(period_start_date) -1 period_end_date
968 from msc_safety_stocks
969 where plan_id = p_plan_id
970 and sr_instance_id = p_sr_instance_id
971 and organization_id = p_organization_id
972 and inventory_item_id = p_inventory_item_id
973 and period_start_date > p_period_start_date;
974
975 cursor c2 (p_plan_id in number) IS
976 select CURR_CUTOFF_DATE
977 from msc_plans
978 where plan_id = p_plan_id;
979
980 CURSOR c3 (p_date IN DATE) IS
981 SELECT end_date
982 FROM msd_dem_dates
983 WHERE p_date BETWEEN start_date AND end_date;
984
985 l_ret date := null;
986 Begin
987
988 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id, p_period_start_date);
989 fetch c1 into l_ret;
990 close c1;
991
992 if l_ret is null then
993 open c2 (p_plan_id);
994 fetch c2 into l_ret;
995 close c2;
996
997 if (upper(msd_dem_common_utilities.dm_time_level) <> 'DAY') then
998 open c3(l_ret);
999 fetch c3 into l_ret;
1000 close c3;
1001 end if;
1002
1003 end if;
1004
1005 return l_ret;
1006 EXCEPTION when others then return NULL;
1007
1008 End get_safety_stock_enddate;
1009
1010
1011 /*
1012 * Returns a valid date from the table INPUTS in Demantra
1013 */
1014 FUNCTION GET_PERIOD_DATE_FOR_DUMMY
1015 RETURN DATE
1016 IS
1017 x_dummy_date DATE := NULL;
1018 BEGIN
1019
1020 EXECUTE IMMEDIATE 'SELECT datet FROM ( '
1021 || ' SELECT datet FROM '
1022 || msd_dem_common_utilities.get_lookup_value ('MSD_DEM_TABLES', 'INPUTS')
1023 || ' WHERE datet > sysdate '
1024 || ' ORDER BY datet ) '
1025 || ' WHERE rownum < 2 '
1026 INTO x_dummy_date;
1027
1028 RETURN x_dummy_date;
1029
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032 RETURN NULL;
1033 END GET_PERIOD_DATE_FOR_DUMMY;
1034
1035
1036
1037 /*
1038 * Given, the instance, customer and/or site, this function returns
1039 * the site level member name. If only the customer is specified then
1040 * then any arbit site belonging to the customer is returned.
1041 */
1042 FUNCTION GET_SITE_FOR_CSF (
1043 p_sr_instance_id IN NUMBER,
1044 p_customer_id IN NUMBER,
1045 p_customer_site_id IN NUMBER)
1046 RETURN VARCHAR2
1047 IS
1048 x_site VARCHAR2(255);
1049 x_dummy_site VARCHAR2(100) := msd_dem_sr_util.get_null_code;
1050 BEGIN
1051
1052 IF (p_customer_site_id IS NOT NULL)
1053 THEN
1054
1055 SELECT substrb(mtp.partner_name, 1, 50)
1056 || ':' || mtil.sr_cust_account_number
1057 || ':' || mtps.location
1058 || ':' || mtps.operating_unit_name
1059 INTO x_site
1060 FROM msc_trading_partner_sites mtps,
1061 msc_trading_partners mtp,
1062 msc_tp_id_lid mtil
1063 WHERE
1064 mtps.partner_site_id = p_customer_site_id
1065 AND mtp.partner_id = mtps.partner_id
1066 AND mtil.tp_id = mtp.partner_id
1067 AND mtil.sr_instance_id = p_sr_instance_id;
1068
1069 ELSIF (p_customer_id IS NOT NULL)
1070 THEN
1071
1072 SELECT substrb(mtp.partner_name, 1, 50)
1073 || ':' || mtil.sr_cust_account_number
1074 || ':' || mtps.location
1075 || ':' || mtps.operating_unit_name
1076 INTO x_site
1077 FROM msc_trading_partners mtp,
1078 msc_tp_id_lid mtil,
1079 msc_trading_partner_sites mtps,
1080 msc_tp_site_id_lid mtsil
1081 WHERE
1082 mtp.partner_id = p_customer_id
1083 AND mtil.tp_id = mtp.partner_id
1084 AND mtil.sr_instance_id = 21
1085 AND mtps.partner_id = mtp.partner_id
1086 AND mtps.tp_site_code = 'SHIP_TO'
1087 AND mtsil.tp_site_id = mtps.partner_site_id
1088 AND mtsil.sr_instance_id = p_sr_instance_id
1089 AND rownum <2;
1090
1091 ELSE
1092 x_site := x_dummy_site;
1093 END IF;
1094
1095 RETURN x_site;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 RETURN x_dummy_site;
1100 END GET_SITE_FOR_CSF;
1101
1102
1103
1104 /*
1105 * Given, the instance, calendar_code, calendar_date, this function
1106 * returns 1 if the date is the last date in its demantra bucket,
1107 * else returns 2.
1108 * Note: This function requires the table msd_dem_dates to be
1109 * populated.
1110 */
1111 FUNCTION IS_LAST_DATE_IN_BUCKET (
1112 p_sr_instance_id IN NUMBER,
1113 p_calendar_code IN VARCHAR2,
1114 p_calendar_date IN DATE)
1115 RETURN NUMBER
1116 IS
1117 x_max_date DATE := NULL;
1118 BEGIN
1119
1120 IF (upper(msd_dem_common_utilities.dm_time_level) = 'DAY')
1121 THEN
1122 RETURN 1;
1123 END IF;
1124
1125 SELECT max(mcd.calendar_date)
1126 INTO x_max_date
1127 FROM msd_dem_dates mdd,
1128 msc_calendar_dates mcd
1129 WHERE
1130 p_calendar_date BETWEEN mdd.start_date AND mdd.end_date
1131 AND mcd.sr_instance_id = p_sr_instance_id
1132 AND mcd.calendar_code = p_calendar_code
1133 AND mcd.exception_set_id = -1
1134 AND mcd.calendar_date BETWEEN mdd.start_date AND mdd.end_date
1135 AND mcd.seq_num IS NOT NULL;
1136
1137 IF (p_calendar_date = x_max_date)
1138 THEN
1139 RETURN 1;
1140 END IF;
1141
1142 RETURN 2;
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 RETURN 2;
1147 END IS_LAST_DATE_IN_BUCKET;
1148
1149
1150
1151 /*
1152 * Given the plan id of a SNO plan, this function returns
1153 * the cutoff date for the plan.
1154 */
1155 FUNCTION GET_SNO_PLAN_CUTOFF_DATE (
1156 p_plan_id IN NUMBER)
1157 RETURN DATE
1158 IS
1159
1160 x_plan_cutoff_date DATE := NULL;
1161
1162 x_sr_instance_id NUMBER := NULL;
1163 x_organization_id NUMBER := NULL;
1164 x_curr_start_date DATE := NULL;
1165 x_planned_bucket NUMBER := NULL;
1166 x_planned_bucket_type NUMBER := NULL;
1167
1168 x_calendar_code VARCHAR2(100) := NULL;
1169
1170 BEGIN
1171
1172 /* Get Plan Info */
1173 SELECT
1174 sr_instance_id,
1175 organization_id,
1176 curr_start_date,
1177 planned_bucket,
1178 planned_bucket_type
1179 INTO
1180 x_sr_instance_id,
1181 x_organization_id,
1182 x_curr_start_date,
1183 x_planned_bucket,
1184 x_planned_bucket_type
1185 FROM
1186 msc_plans
1187 WHERE
1188 plan_id = p_plan_id;
1189
1190 /* Get calendar for the plan owning org */
1191 SELECT
1192 calendar_code
1193 INTO
1194 x_calendar_code
1195 FROM
1196 msc_trading_partners
1197 WHERE
1198 partner_type = 3
1199 AND sr_tp_id = x_organization_id
1200 AND sr_instance_id = x_sr_instance_id;
1201
1202 /* Get cut-off date */
1203 IF (x_planned_bucket_type = 2) /* WEEK */
1204 THEN
1205
1206 SELECT
1207 max(next_date) - 1
1208 INTO x_plan_cutoff_date
1209 FROM
1210 ( SELECT
1211 next_date
1212 FROM
1213 msc_cal_week_start_dates
1214 WHERE
1215 calendar_code = x_calendar_code
1216 AND sr_instance_id = x_sr_instance_id
1217 AND week_start_date > x_curr_start_date
1218 ORDER BY next_date)
1219 WHERE
1220 rownum < x_planned_bucket + 1;
1221
1222 ELSIF (x_planned_bucket_type = 3) /* PERIOD */
1223 THEN
1224
1225 SELECT
1226 max(next_date) - 1
1227 INTO x_plan_cutoff_date
1228 FROM
1229 ( SELECT
1230 next_date
1231 FROM
1232 msc_period_start_dates
1233 WHERE
1234 calendar_code = x_calendar_code
1235 AND sr_instance_id = x_sr_instance_id
1236 AND period_start_date > x_curr_start_date
1237 ORDER BY next_date)
1238 WHERE
1239 rownum < x_planned_bucket + 1;
1240
1241 ELSE
1242 RETURN NULL;
1243 END IF;
1244
1245 RETURN x_plan_cutoff_date;
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 RETURN NULL;
1250 END GET_SNO_PLAN_CUTOFF_DATE;
1251
1252
1253
1254 /*
1255 * This function returns 1 if a supplier calendar is present else returns 2.
1256 */
1257 FUNCTION IS_SUPPLIER_CALENDAR_PRESENT (
1258 p_plan_id IN NUMBER,
1259 p_sr_instance_id IN NUMBER,
1260 p_organization_id IN NUMBER,
1261 p_inventory_item_id IN NUMBER,
1262 p_supplier_id IN NUMBER,
1263 p_supplier_site_id IN NUMBER,
1264 p_using_organization_id IN NUMBER)
1265 RETURN NUMBER
1266 IS
1267
1268 cursor c1 (p_plan_id in number, p_sr_instance_id IN NUMBER, p_organization_id IN number, p_inventory_item_id IN NUMBER,
1269 p_supplier_id in number, p_supplier_site_id in number, p_using_organization_id in number) IS
1270 select DELIVERY_CALENDAR_CODE
1271 from msc_item_suppliers
1272 where plan_id = p_plan_id
1273 and sr_instance_id = p_sr_instance_id
1274 and organization_id = p_organization_id
1275 and inventory_item_id = p_inventory_item_id
1276 and supplier_id = p_supplier_id
1277 and supplier_site_id = p_supplier_site_id
1278 and using_organization_id = p_using_organization_id;
1279
1280 l_ret varchar2(30) := null;
1281 l_ret1 number := 2;
1282
1283 BEGIN
1284
1285 open c1 (p_plan_id, p_sr_instance_id, p_organization_id, p_inventory_item_id,
1286 p_supplier_id, p_supplier_site_id, p_using_organization_id);
1287 fetch c1 into l_ret;
1288 close c1;
1289
1290 if l_ret is not null then
1291 l_ret1 := 1;
1292 end if;
1293
1294 RETURN l_ret1;
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 RETURN 2;
1299
1300 END IS_SUPPLIER_CALENDAR_PRESENT;
1301
1302 END MSD_DEM_COMMON_UTILITIES;
[Home] [Help]