[Home] [Help]
PACKAGE BODY: APPS.MSC_SCN_UTILS
Source
1 PACKAGE BODY MSC_SCN_UTILS AS
2 /* $Header: MSCSCNUB.pls 120.65.12020000.3 2013/02/05 20:49:05 wexia ship $*/
3
4 p_version constant number := 7;
5
6
7 Procedure insert_seed_data is
8 Cursor tcur is
9 select count(*)
10 from msc_activity_parameters
11 where param_version >= p_version;
12
13 ln_count number := 0;
14 begin
15 /* if g_refresh = 0 then
16 return;
17 end if;
18 g_refresh := 0;*/
19
20
21
22
23
24
25 /************
26 select lookup_code, meaning
27 from mfg_lookups
28 where lookup_type='MSC_PROCESS_ACTIVITY_TYPES'
29
30 LOOKUP_CODE MEANING
31 ----------- -----------------------------------
32 1 Run Supply Chain Plan
33 10 Run Demantra Collections and Download
34 11 Run ASCP Collections
35 12 Generate Forecast
36 13 Review Forecast
37 14 Review Supply Chain Plan
38 15 Review Financial Plan
39 16 Review Marketing Plan
40 17 Review Demand Plan
41 18 Review Sales Plan
42 19 Upload Forecast
43 20 Review Supply Network Plan
44 21 Approve Consensus Demand
45 22 Executive Review
46 4 Run Inventory Plan
47 6 Run Supply Network Plan
48
49
50 msc_activity_parameters
51 ACTIVITY_TYPE NOT NULL NUMBER
52 NAME NOT NULL VARCHAR2(30)
53 DATA_TYPE VARCHAR2(30)
54 DEFAULT_VALUE VARCHAR2(30)
55 SEQUENCE NUMBER
56 SQL VARCHAR2(2000)
57 CREATED_BY NUMBER
58 CREATION_DATE DATE
59 LAST_UPDATE_DATE DATE
60 LAST_UPDATED_BY NUMBER
61 LAST_UPDATE_LOGIN NUMBER
62 LOOKUP_TYPE VARCHAR2(30)
63 REQUIRED VARCHAR2(10)
64 DISPLAYED VARCHAR2(10)
65 DISPLAY_NAME, PARAM_VERSION VARCHAR2(64)
66 ENABLED NUMBER -- 1, YES, 2/NULL - NO
67 COMPONENT_STYLE NUMBER -- 1- Message text input
68 -- 2- LOV
69 -- 3- Date
70 PARAM_VERSION NUMBER
71
72 *********************************************************/
73 open tcur;
74 fetch tcur into ln_count;
75 close tcur;
76 if nvl(ln_count,0) < 1 then
77
78 delete from msc_activity_parameters;
79 commit;
80
81 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
82 NAME,
83 DATA_TYPE,
84 DEFAULT_VALUE,
85 SEQUENCE,
86 SQL,
87 CREATED_BY,
88 CREATION_DATE,
89 REQUIRED,
90 DISPLAYED,
91 DISPLAY_NAME,
92 PARAM_VERSION)
93 VALUES(1,'PLAN_ID','NUMBER',NULL,1,
94 '',
95 1,SYSDATE,'Y','N','Plan Name',p_version);
96
97 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
98 NAME,
99 DATA_TYPE,
100 DEFAULT_VALUE,
101 SEQUENCE,
102 SQL,
103 CREATED_BY,
104 CREATION_DATE,
105 REQUIRED,
106 DISPLAYED,
107 DISPLAY_NAME,
108 PARAM_VERSION)
109 VALUES(1,'LAUNCH_SNAPSHOT','NUMBER','FULL',2,
110 'select decode(lookup_code, 1, ''FULL'',2,''NO'',3,''DP_ONLY'') hidden, meaning display '||
111 'from mfg_lookups '||
112 'where lookup_type = ''MSC_LAUNCH_SNAPSHOT''',
113 1,SYSDATE,'Y','Y','Launch Snapshot',p_version);
114
115 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
116 NAME,
117 DATA_TYPE,
118 DEFAULT_VALUE,
119 SEQUENCE,
120 SQL,
121 CREATED_BY,
122 CREATION_DATE,
123 REQUIRED,
124 DISPLAYED,
125 DISPLAY_NAME,
126 PARAM_VERSION)
127 VALUES(1,'LAUNCH_PLANNER','NUMBER','Y',3,
128 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
129 'from mfg_lookups '||
130 'where lookup_type = ''SYS_YES_NO''',
131 1,SYSDATE,'Y','Y','Launch Planner',p_version);
132
133
134 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
135 NAME,
136 DATA_TYPE,
137 DEFAULT_VALUE,
138 SEQUENCE,
139 SQL,
140 CREATED_BY,
141 CREATION_DATE,
142 REQUIRED,
143 DISPLAYED,
144 DISPLAY_NAME,
145 PARAM_VERSION)
146 VALUES(1,'ANCHOR_DATE','DATE',SYSDATE,4,
147 '',
148 1,SYSDATE,'Y','Y','Anchor Date',p_version);
149
150 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
151 NAME,
152 DATA_TYPE,
153 DEFAULT_VALUE,
154 SEQUENCE,
155 SQL,
156 CREATED_BY,
157 CREATION_DATE,
158 REQUIRED,
159 DISPLAYED,
160 DISPLAY_NAME,
161 PARAM_VERSION)
162 VALUES(1,'ARCHIVE_FLAG','VARCHAR2','N',5,
163 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
164 1,sysdate,'Y','Y','Archive Plan Summary',p_version);
165
166
167 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
168 NAME,
169 DATA_TYPE,
170 DEFAULT_VALUE,
171 SEQUENCE,
172 SQL,
173 CREATED_BY,
174 CREATION_DATE,
175 REQUIRED,
176 DISPLAYED,
177 DISPLAY_NAME,
178 PARAM_VERSION)
179 VALUES(1,'ENABLE_24X7ATP','NUMBER',NULL,6,
180 'select decode(lookup_code, 1, ''YES_PURGE'',2,''NO'',3,''YES_NO_PURGE'') hidden, meaning display '||
181 'from mfg_lookups '||
182 'where lookup_type = ''MSC_24X7_PURGE''',
183 1,SYSDATE,'Y','Y','Enable 24x7ATP',p_version);
184
185
186 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
187 NAME,
188 DATA_TYPE,
189 DEFAULT_VALUE,
190 SEQUENCE,
191 SQL,
192 CREATED_BY,
193 CREATION_DATE,
194 REQUIRED,
195 DISPLAYED,
196 DISPLAY_NAME,
197 PARAM_VERSION)
198 VALUES(1,'RELEASE_RESCHEDULES','NUMBER','N',7,
199 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
200 'from mfg_lookups '||
201 'where lookup_type = ''SYS_YES_NO''',
202 1,SYSDATE,'Y','Y','Release Reschedules',p_version);
203
204 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
205 NAME,
206 DATA_TYPE,
207 DEFAULT_VALUE,
208 SEQUENCE,
209 SQL,
210 CREATED_BY,
211 CREATION_DATE,
212 REQUIRED,
213 DISPLAYED,
214 DISPLAY_NAME,
215 PARAM_VERSION)
216 VALUES(1,'SNAP_STATIC_ENTITIES','NUMBER','Y',8,
217 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
218 'from mfg_lookups '||
219 'where lookup_type = ''SYS_YES_NO''',
220 1,SYSDATE,'Y','Y','Snapshot Static Entities',p_version);
221
222 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
223 NAME,
224 DATA_TYPE,
225 DEFAULT_VALUE,
226 SEQUENCE,
227 SQL,
228 CREATED_BY,
229 CREATION_DATE,
230 REQUIRED,
231 DISPLAYED,
232 DISPLAY_NAME,
233 PARAM_VERSION)
234 VALUES(6,'PLAN_ID','VARCHAR2',NULL,1,
235 ' SELECT plans.compile_designator hidden,'||
236 ' plans.compile_designator display'||
237 ' FROM msc_plans plans, msc_designators desig'||
238 ' WHERE plans.organization_id = NVL(fnd_profile.value(''SCENARIO_PLANNING_ORG''),plans.organization_id)'||
239 ' AND plans.sr_instance_id = NVL(fnd_profile.value(''SCENARIO_PLANNING_INST''),plans.sr_instance_id)'||
240 ' AND plans.curr_plan_type = 6 '||
241 ' AND plans.organization_id = desig.organization_id'||
242 ' AND plans.sr_instance_id = desig.sr_instance_id'||
243 ' AND plans.compile_designator = desig.designator'||
244 ' AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)'||
245 ' AND plans.organization_selection <> 1'||
246 ' AND NVL(plans.copy_plan_id,-1) = -1'||
247 ' AND NVL(desig.copy_designator_id, -1) = -1',
248 1,SYSDATE,'Y','N','Plan Name',p_version);
249
250 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
251 NAME,
252 DATA_TYPE,
253 DEFAULT_VALUE,
254 SEQUENCE,
255 SQL,
256 CREATED_BY,
257 CREATION_DATE,
258 REQUIRED,
259 DISPLAYED,
260 DISPLAY_NAME,
261 PARAM_VERSION)
262 VALUES(6,'SOLVE_IN_SERVER','VARCHAR2','Y',2,
263 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display '||
264 'from mfg_lookups '||
265 'where lookup_type = ''SYS_YES_NO''',
266 1,SYSDATE,'Y','Y','Solve in Server',p_version);
267
268 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
269 NAME,
270 DATA_TYPE,
271 DEFAULT_VALUE,
272 SEQUENCE,
273 SQL,
274 CREATED_BY,
275 CREATION_DATE,
276 REQUIRED,
277 DISPLAYED,
278 DISPLAY_NAME,
279 PARAM_VERSION)
280 VALUES(6,'appProfile','VARCHAR2',NULL,3,
281 'select decode(lookup_code, 1, ''SOP'',2,''SCRM'',3,''SNO'') hidden, meaning display from mfg_lookups where lookup_type=''MSC_SCN_SOP_PROFILE''',
282 1,SYSDATE,'Y','Y','AppProfile',p_version);
283
284
285
286
287 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
288 NAME,
289 DATA_TYPE,
290 DEFAULT_VALUE,
291 SEQUENCE,
292 SQL,
293 CREATED_BY,
294 CREATION_DATE,
295 REQUIRED,
296 DISPLAYED,
297 DISPLAY_NAME,
298 PARAM_VERSION)
299 VALUES(4,'PLAN_ID','NUMBER',NULL,1,
300 '',
301 1,SYSDATE,'Y','N','Plan Name',p_version);
302
303 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
304 NAME,
305 DATA_TYPE,
306 DEFAULT_VALUE,
307 SEQUENCE,
308 SQL,
309 CREATED_BY,
310 CREATION_DATE,
311 REQUIRED,
312 DISPLAYED,
313 DISPLAY_NAME,
314 PARAM_VERSION)
315 VALUES(4,'ANCHOR_DATE','DATE',SYSDATE,2,
316 '',
317 1,SYSDATE,'Y','Y','Anchor Date',p_version);
318
319
320
321 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
322 NAME,
323 DATA_TYPE,
324 DEFAULT_VALUE,
325 SEQUENCE,
326 SQL,
327 CREATED_BY,
328 CREATION_DATE,
329 REQUIRED,
330 DISPLAYED,
331 DISPLAY_NAME,
332 PARAM_VERSION)
333 VALUES(4,'ARCHIVE_FLAG','VARCHAR2','N',3,
334 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
335 1,sysdate,'Y','Y','Archive Plan Summary',p_version);
336
337
338 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
339 NAME,
340 DATA_TYPE,
341 DEFAULT_VALUE,
342 SEQUENCE,
343 SQL,
344 CREATED_BY,
345 CREATION_DATE,
346 REQUIRED,
347 DISPLAYED,
348 DISPLAY_NAME,
349 PARAM_VERSION)
350 VALUES(11,'INSTANCE_ID','NUMBER',NULL,1,
351 'select INSTANCE_ID hidden, INSTANCE_CODE Display '||
352 'FROM MSC_APPS_INSTANCES '||
353 'where instance_type IN (1,2,4) and enable_flag = 1',
354 1,SYSDATE,'Y','Y','Instance',p_version);
355
356 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
357 NAME,
358 DATA_TYPE,
359 DEFAULT_VALUE,
360 SEQUENCE,
361 SQL,
362 CREATED_BY,
363 CREATION_DATE,
364 REQUIRED,
365 DISPLAYED,
366 DISPLAY_NAME,
367 PARAM_VERSION)
368 VALUES(11,'COLLECTION_GROUP','VARCHAR2',NULL,2,
369 'select CODE hidden, ORG_GROUP Display FROM MSC_ORG_GROUPS_V ' ,
370 1,SYSDATE,'Y','Y','Collections Group',p_version);
371
372 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
373 NAME,
374 DATA_TYPE,
375 DEFAULT_VALUE,
376 SEQUENCE,
377 SQL,
378 CREATED_BY,
379 CREATION_DATE,
380 REQUIRED,
381 DISPLAYED,
382 DISPLAY_NAME,
383 PARAM_VERSION)
384 VALUES(11,'TOTAL_WORKER_NUM','NUMBER',3,3,NULL,1,SYSDATE,'Y','Y','Number of workers',p_version);
385
386 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
387 NAME,
388 DATA_TYPE,
389 DEFAULT_VALUE,
390 SEQUENCE,
391 SQL,
392 CREATED_BY,
393 CREATION_DATE,
394 REQUIRED,
395 DISPLAYED,
396 DISPLAY_NAME,
397 PARAM_VERSION)
398 VALUES(11,'TIME_OUT','NUMBER',180,4,NULL,1,SYSDATE,'Y','Y','Timeout (Minutes)',p_version);
399
400 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
401 NAME,
402 DATA_TYPE,
403 DEFAULT_VALUE,
404 SEQUENCE,
405 SQL,
406 CREATED_BY,
407 CREATION_DATE,
408 REQUIRED,
409 DISPLAYED,
410 DISPLAY_NAME,
411 PARAM_VERSION)
412 VALUES(11,'ODS_PURGE_OPTION','VARCHAR2','Y',5,
413 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
414 1,SYSDATE,'Y','Y','Purge Previously Collected Data',p_version);
415
416 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
417 NAME,
418 DATA_TYPE,
419 DEFAULT_VALUE,
420 SEQUENCE,
421 SQL,
422 CREATED_BY,
423 CREATION_DATE,
424 REQUIRED,
425 DISPLAYED,
426 DISPLAY_NAME,
427 PARAM_VERSION)
428 VALUES(11,'COLLECTION_METHOD','VARCHAR2','COMPLETE_REFRESH',6,
429 'select decode(lookup_code, 1, ''COMPLETE_REFRESH'',2,''NET_CHANGE_REFRESH'',3,''TARGETED_REFRESH'') hidden, meaning display ' ||
430 'from mfg_lookups WHERE lookup_type = ''PARTIAL_YES_NO''',
431 1,SYSDATE,'Y','Y','Collection Method',p_version);
432
433 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
434 NAME,
435 DATA_TYPE,
436 DEFAULT_VALUE,
437 SEQUENCE,
438 SQL,
439 CREATED_BY,
440 CREATION_DATE,
441 REQUIRED,
442 DISPLAYED,
443 DISPLAY_NAME,
444 PARAM_VERSION)
445 VALUES(11,'ANALYZE_TABLES_ENABLED','VARCHAR2','N',7,
446 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
447 1,SYSDATE,'Y','Y','Analyze Staging Tables',p_version);
448
449 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
450 NAME,
451 DATA_TYPE,
452 DEFAULT_VALUE,
453 SEQUENCE,
454 SQL,
455 CREATED_BY,
456 CREATION_DATE,
457 REQUIRED,
458 DISPLAYED,
459 DISPLAY_NAME,
460 PARAM_VERSION)
461 VALUES(11,'APPROVED_SUPPLIER_LIST','VARCHAR2','YES_REPLACE',8,
462 ' select decode(lookup_code, 1, ''YES_REPLACE'',2,''NO'',3,''YES_BUT_RETAIN_CP'') hidden, meaning display '||
463 'from mfg_lookups where lookup_type=''MSC_X_ASL_SYS_YES_NO''',
464 1,SYSDATE,'Y','Y','Collect Approved Supplier Lists (Supplier Capacities)',p_version);
465
466 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
467 NAME,
468 DATA_TYPE,
469 DEFAULT_VALUE,
470 SEQUENCE,
471 SQL,
472 CREATED_BY,
473 CREATION_DATE,
474 REQUIRED,
475 DISPLAYED,
476 DISPLAY_NAME,
477 PARAM_VERSION)
478 VALUES(11,'ATP_RULES_ENABLED','VARCHAR2','Y',9,
479 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
480 1,SYSDATE,'Y','Y','Collect ATP Rules',p_version);
481
482 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
483 NAME,
484 DATA_TYPE,
485 DEFAULT_VALUE,
486 SEQUENCE,
487 SQL,
488 CREATED_BY,
489 CREATION_DATE,
490 REQUIRED,
491 DISPLAYED,
492 DISPLAY_NAME,
493 PARAM_VERSION)
494 VALUES(11,'BOM_ENABLED','VARCHAR2','Y',10,
495 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
496 1,SYSDATE,'Y','Y','Collect Bill of Materials/Routings/Resources',p_version);
497
498 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
499 NAME,
500 DATA_TYPE,
501 DEFAULT_VALUE,
502 SEQUENCE,
503 SQL,
504 CREATED_BY,
505 CREATION_DATE,
506 REQUIRED,
507 DISPLAYED,
508 DISPLAY_NAME,
509 PARAM_VERSION)
510 VALUES(11,'BOR_ENABLED','VARCHAR2','Y',11,
511 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
512 1,SYSDATE,'Y','Y','Collect Bills of Resources',p_version);
513
514 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
515 NAME,
516 DATA_TYPE,
517 DEFAULT_VALUE,
518 SEQUENCE,
519 SQL,
520 CREATED_BY,
521 CREATION_DATE,
522 REQUIRED,
523 DISPLAYED,
524 DISPLAY_NAME,
525 PARAM_VERSION)
526 VALUES(11,'CALENDAR_ENABLED','VARCHAR2','Y',12,
527 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
528 1,SYSDATE,'Y','Y','Collect Calendars',p_version);
529
530 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
531 NAME,
532 DATA_TYPE,
533 DEFAULT_VALUE,
534 SEQUENCE,
535 SQL,
536 CREATED_BY,
537 CREATION_DATE,
538 REQUIRED,
539 DISPLAYED,
540 DISPLAY_NAME,
541 PARAM_VERSION)
542 VALUES(11,'DEMAND_CLASS_ENABLED','VARCHAR2','Y',13,
543 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
544 1,SYSDATE,'Y','Y','Collect Demand Classes',p_version);
545
546 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
547 NAME,
548 DATA_TYPE,
549 DEFAULT_VALUE,
550 SEQUENCE,
551 SQL,
552 CREATED_BY,
553 CREATION_DATE,
554 REQUIRED,
555 DISPLAYED,
556 DISPLAY_NAME,
557 PARAM_VERSION)
558 VALUES(11,'ITEM_SUBST_ENABLED','VARCHAR2','Y',14,
559 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
560 1,SYSDATE,'Y','Y','Collect End Item Substitutions',p_version);
561
562 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
563 NAME,
564 DATA_TYPE,
565 DEFAULT_VALUE,
566 SEQUENCE,
567 SQL,
568 CREATED_BY,
569 CREATION_DATE,
570 REQUIRED,
571 DISPLAYED,
572 DISPLAY_NAME,
573 PARAM_VERSION)
574 VALUES(11,'FORECAST_ENABLED','VARCHAR2','Y',15,
575 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
576 1,SYSDATE,'Y','Y','Collect Forecasts',p_version);
577
578 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
579 NAME,
580 DATA_TYPE,
581 DEFAULT_VALUE,
582 SEQUENCE,
583 SQL,
584 CREATED_BY,
585 CREATION_DATE,
586 REQUIRED,
587 DISPLAYED,
588 DISPLAY_NAME,
589 PARAM_VERSION)
590 VALUES(11,'ITEM_ENABLED','VARCHAR2','Y',16,
591 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
592 1,SYSDATE,'Y','Y','Collect Items',p_version);
593
594 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
595 NAME,
596 DATA_TYPE,
597 DEFAULT_VALUE,
598 SEQUENCE,
599 SQL,
600 CREATED_BY,
601 CREATION_DATE,
602 REQUIRED,
603 DISPLAYED,
604 DISPLAY_NAME,
605 PARAM_VERSION)
606 VALUES(11,'KPI_BIS_ENABLED','VARCHAR2','Y',17,
607 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
608 1,SYSDATE,'Y','Y','Collect Key Performance Indicator Targets',p_version);
609
610 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
611 NAME,
612 DATA_TYPE,
613 DEFAULT_VALUE,
614 SEQUENCE,
615 SQL,
616 CREATED_BY,
617 CREATION_DATE,
618 REQUIRED,
619 DISPLAYED,
620 DISPLAY_NAME,
621 PARAM_VERSION)
622 VALUES(11,'MDS_ENABLED','VARCHAR2','Y',18,
623 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
624 1,SYSDATE,'Y','Y','Collect Master Demand Schedules',p_version);
625
626 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
627 NAME,
628 DATA_TYPE,
629 DEFAULT_VALUE,
630 SEQUENCE,
631 SQL,
632 CREATED_BY,
633 CREATION_DATE,
634 REQUIRED,
635 DISPLAYED,
636 DISPLAY_NAME,
637 PARAM_VERSION)
638 VALUES(11,'MPS_ENABLED','VARCHAR2','Y',19,
639 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
640 1,SYSDATE,'Y','Y','Collect Master Production Schedules',p_version);
641
642 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
643 NAME,
644 DATA_TYPE,
645 DEFAULT_VALUE,
646 SEQUENCE,
647 SQL,
648 CREATED_BY,
649 CREATION_DATE,
650 REQUIRED,
651 DISPLAYED,
652 DISPLAY_NAME,
653 PARAM_VERSION)
654 VALUES(11,'ON_HAND_ENABLED','VARCHAR2','Y',20,
655 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
656 1,SYSDATE,'Y','Y','Collect On Hand',p_version);
657
658 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
659 NAME,
660 DATA_TYPE,
661 DEFAULT_VALUE,
662 SEQUENCE,
663 SQL,
664 CREATED_BY,
665 CREATION_DATE,
666 REQUIRED,
667 DISPLAYED,
668 DISPLAY_NAME,
669 PARAM_VERSION)
670 VALUES(11,'PARAMETER_ENABLED','VARCHAR2','Y',21,
671 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
672 1,SYSDATE,'Y','Y','Collect Planning Parameters',p_version);
673
674 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
675 NAME,
676 DATA_TYPE,
677 DEFAULT_VALUE,
678 SEQUENCE,
679 SQL,
680 CREATED_BY,
681 CREATION_DATE,
682 REQUIRED,
683 DISPLAYED,
684 DISPLAY_NAME,
685 PARAM_VERSION)
686 VALUES(11,'PLANNER_ENABLED','VARCHAR2','Y',22,
687 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
688 1,SYSDATE,'Y','Y','Planner',p_version);
689
690 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
691 NAME,
692 DATA_TYPE,
693 DEFAULT_VALUE,
694 SEQUENCE,
695 SQL,
696 CREATED_BY,
697 CREATION_DATE,
698 REQUIRED,
699 DISPLAYED,
700 DISPLAY_NAME,
701 PARAM_VERSION)
702 VALUES(11,'PO_RECEIPTS_ENABLED','VARCHAR2','Y',23,
703 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
704 1,SYSDATE,'Y','Y','Collect PO Receipts',p_version);
705
706 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
707 NAME,
708 DATA_TYPE,
709 DEFAULT_VALUE,
710 SEQUENCE,
711 SQL,
712 CREATED_BY,
713 CREATION_DATE,
714 REQUIRED,
715 DISPLAYED,
716 DISPLAY_NAME,
717 PARAM_VERSION)
718 VALUES(11,'PROJECT_ENABLED','VARCHAR2','Y',24,
719 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
720 1,SYSDATE,'Y','Y','Collect Projects / Tasks',p_version);
721
722 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
723 NAME,
724 DATA_TYPE,
725 DEFAULT_VALUE,
726 SEQUENCE,
727 SQL,
728 CREATED_BY,
729 CREATION_DATE,
730 REQUIRED,
731 DISPLAYED,
732 DISPLAY_NAME,
733 PARAM_VERSION)
734 VALUES(11,'PUR_REQ_PO_ENABLED','VARCHAR2','Y',25,
735 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
736 1,SYSDATE,'Y','Y','Collect Purchase Orders / Purchase Requisitions',p_version);
737
738 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
739 NAME,
740 DATA_TYPE,
741 DEFAULT_VALUE,
742 SEQUENCE,
743 SQL,
744 CREATED_BY,
745 CREATION_DATE,
746 REQUIRED,
747 DISPLAYED,
748 DISPLAY_NAME,
749 PARAM_VERSION)
750 VALUES(11,'RESERVES_HARD_ENABLED','VARCHAR2','Y',26,
751 'select decode(lookup_code, 1, ''Y'',2,''N'')hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
752 1,SYSDATE,'Y','Y','Reservations',p_version);
753
754 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
755 NAME,
756 DATA_TYPE,
757 DEFAULT_VALUE,
758 SEQUENCE,
759 SQL,
760 CREATED_BY,
761 CREATION_DATE,
762 REQUIRED,
763 DISPLAYED,
764 DISPLAY_NAME,
765 PARAM_VERSION)
766 VALUES(11,'RESOURCE_AVAILABILITY','VARCHAR2','COLLECT_DATA',27,
767 'select decode(lookup_code, 1, ''COLLECT_DATA'', 2, ''DO_NOT_COLLECT_DATA'', 3, ''REGENERATE_DATA'') hidden, meaning display from MFG_LOOKUPS where lookup_type=''MSC_NRA_ENABLED''',
768 1,SYSDATE,'Y','Y','Collect Resources Availability',p_version);
769
770 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
771 NAME,
772 DATA_TYPE,
773 DEFAULT_VALUE,
774 SEQUENCE,
775 SQL,
776 CREATED_BY,
777 CREATION_DATE,
778 REQUIRED,
779 DISPLAYED,
780 DISPLAY_NAME,
781 PARAM_VERSION)
782 VALUES(11,'SAFE_STOCK_ENABLED','VARCHAR2','Y',28,
783 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
784 1,SYSDATE,'Y','Y','Collect Safety Stock',p_version);
785
786 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
787 NAME,
788 DATA_TYPE,
789 DEFAULT_VALUE,
790 SEQUENCE,
791 SQL,
792 CREATED_BY,
793 CREATION_DATE,
794 REQUIRED,
795 DISPLAYED,
796 DISPLAY_NAME,
797 PARAM_VERSION)
798 VALUES(11,'SALES_ORDER_RTYPE','VARCHAR2','N',29,
799 'select decode(lookup_code, 1, ''Y'', 2, ''N'') hidden, meaning display from mfg_lookups WHERE lookup_type = ''SYS_YES_NO''',
800 1,SYSDATE,'Y','Y','Collect Sales Orders',p_version);
801
802 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
803 NAME,
804 DATA_TYPE,
805 DEFAULT_VALUE,
806 SEQUENCE,
807 SQL,
808 CREATED_BY,
809 CREATION_DATE,
810 REQUIRED,
811 DISPLAYED,
812 DISPLAY_NAME,
813 PARAM_VERSION)
814 VALUES(11,'SOURCING_HISTORY_ENABLED','VARCHAR2','N',30,
815 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
816 1,SYSDATE,'Y','Y','Collect Sourcing History',p_version);
817
818 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
819 NAME,
820 DATA_TYPE,
821 DEFAULT_VALUE,
822 SEQUENCE,
823 SQL,
824 CREATED_BY,
825 CREATION_DATE,
826 REQUIRED,
827 DISPLAYED,
828 DISPLAY_NAME,
829 PARAM_VERSION)
830 VALUES(11,'SOURCING_ENABLED','VARCHAR2','Y',31,
831 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
832 1,SYSDATE,'Y','Y','Collect Sourcing Rules',p_version);
833
834 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
835 NAME,
836 DATA_TYPE,
837 DEFAULT_VALUE,
838 SEQUENCE,
839 SQL,
840 CREATED_BY,
841 CREATION_DATE,
842 REQUIRED,
843 DISPLAYED,
844 DISPLAY_NAME,
845 PARAM_VERSION)
846 VALUES(11,'SUB_INV_ENABLED','VARCHAR2','Y',32,
847 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
848 1,SYSDATE,'Y','Y','Collect Subinventories',p_version);
849
850 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
851 NAME,
852 DATA_TYPE,
853 DEFAULT_VALUE,
854 SEQUENCE,
855 SQL,
856 CREATED_BY,
857 CREATION_DATE,
858 REQUIRED,
859 DISPLAYED,
860 DISPLAY_NAME,
861 PARAM_VERSION)
862 VALUES(11,'SUPPLIER_RESPONSE_ENABLED','VARCHAR2','Y',33,
863 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
864 1,SYSDATE,'Y','Y','Collect Supplier Responses',p_version);
865
866 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
867 NAME,
868 DATA_TYPE,
869 DEFAULT_VALUE,
870 SEQUENCE,
871 SQL,
872 CREATED_BY,
873 CREATION_DATE,
874 REQUIRED,
875 DISPLAYED,
876 DISPLAY_NAME,
877 PARAM_VERSION)
878 VALUES(11,'TP_CUSTOMER_ENABLED','VARCHAR2','Y',34,
879 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
880 1,SYSDATE,'Y','Y','Collect Suppliers/Customers/Orgs',p_version);
881
882 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
883 NAME,
884 DATA_TYPE,
885 DEFAULT_VALUE,
886 SEQUENCE,
887 SQL,
888 CREATED_BY,
889 CREATION_DATE,
890 REQUIRED,
891 DISPLAYED,
892 DISPLAY_NAME,
893 PARAM_VERSION)
894 VALUES(11,'TRIP_ENABLED','VARCHAR2','Y',35,
895 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
896 1,SYSDATE,'Y','Y','Collect Transportation Details',p_version);
897
898 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
899 NAME,
900 DATA_TYPE,
901 DEFAULT_VALUE,
902 SEQUENCE,
903 SQL,
904 CREATED_BY,
905 CREATION_DATE,
906 REQUIRED,
907 DISPLAYED,
908 DISPLAY_NAME,
909 PARAM_VERSION)
910 VALUES(11,'UNIT_NO_ENABLED','VARCHAR2','Y',36,
911 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
912 1,SYSDATE,'Y','Y','Collect Unit Numbers',p_version);
913
914 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
915 NAME,
916 DATA_TYPE,
917 DEFAULT_VALUE,
918 SEQUENCE,
919 SQL,
920 CREATED_BY,
921 CREATION_DATE,
922 REQUIRED,
923 DISPLAYED,
924 DISPLAY_NAME,
925 PARAM_VERSION)
926 VALUES(11,'UOM_ENABLED','VARCHAR2','Y',37,
927 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
928 1,SYSDATE,'Y','Y','Collect Units Of Measure',p_version);
929
930 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
931 NAME,
932 DATA_TYPE,
933 DEFAULT_VALUE,
934 SEQUENCE,
935 SQL,
936 CREATED_BY,
937 CREATION_DATE,
938 REQUIRED,
939 DISPLAYED,
940 DISPLAY_NAME,
941 PARAM_VERSION)
942 VALUES(11,'USER_COMPANY_ENABLED','VARCHAR2','NO',38,
943 'select decode(lookup_code, 1, ''NO'', 2, ''ENABLE_UCA'', 3, ''CREATE_USERS_ENABLE_UCA'') hidden, meaning display ' ||
944 'from fnd_lookups where lookup_type = ''MSC_X_USER_COMPANY''',
945 1,SYSDATE,'Y','Y','Collect User company Association',p_version);
946
947 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
948 NAME,
949 DATA_TYPE,
950 DEFAULT_VALUE,
951 SEQUENCE,
952 SQL,
953 CREATED_BY,
954 CREATION_DATE,
955 REQUIRED,
956 DISPLAYED,
957 DISPLAY_NAME,
958 PARAM_VERSION)
959 VALUES(11,'USER_SUPPLY_DEMAND','VARCHAR2','Y',39,
960 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
961 1,SYSDATE,'Y','Y','Collect User Supplies and Demands',p_version);
962
963 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
964 NAME,
965 DATA_TYPE,
966 DEFAULT_VALUE,
967 SEQUENCE,
968 SQL,
969 CREATED_BY,
970 CREATION_DATE,
971 REQUIRED,
972 DISPLAYED,
973 DISPLAY_NAME,
974 PARAM_VERSION)
975 VALUES(11,'WIP_ENABLED','VARCHAR2','Y',40,
976 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
977 1,SYSDATE,'Y','Y','Collect Work in Process',p_version);
978
979 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
980 NAME,
981 DATA_TYPE,
982 DEFAULT_VALUE,
983 SEQUENCE,
984 SQL,
985 CREATED_BY,
986 CREATION_DATE,
987 REQUIRED,
988 DISPLAYED,
989 DISPLAY_NAME,
990 PARAM_VERSION)
991 VALUES(11,'SALES_CHANNEL_ENABLED','VARCHAR2','N',41,
992 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
993 1,SYSDATE,'Y','Y','Collect Sales Channel',p_version);
994
995 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
996 NAME,
997 DATA_TYPE,
998 DEFAULT_VALUE,
999 SEQUENCE,
1000 SQL,
1001 CREATED_BY,
1002 CREATION_DATE,
1003 REQUIRED,
1004 DISPLAYED,
1005 DISPLAY_NAME,
1006 PARAM_VERSION)
1007 VALUES(11,'FISCAL_CALENDAR_ENABLED','VARCHAR2','N',42,
1008 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1009 1,SYSDATE,'Y','Y','Collect Fiscal Calendar',p_version);
1010
1011 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1012 NAME,
1013 DATA_TYPE,
1014 DEFAULT_VALUE,
1015 SEQUENCE,
1016 SQL,
1017 CREATED_BY,
1018 CREATION_DATE,
1019 REQUIRED,
1020 DISPLAYED,
1021 DISPLAY_NAME,
1022 PARAM_VERSION)
1023 VALUES(11,'ITERNAL_REPAIR_ENABLED','VARCHAR2','N',43,
1024 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1025 1,SYSDATE,'Y','Y','Collect Internal Repair Orders',p_version);
1026
1027 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1028 NAME,
1029 DATA_TYPE,
1030 DEFAULT_VALUE,
1031 SEQUENCE,
1032 SQL,
1033 CREATED_BY,
1034 CREATION_DATE,
1035 REQUIRED,
1036 DISPLAYED,
1037 DISPLAY_NAME,
1038 PARAM_VERSION)
1039 VALUES(11,'ETERNAL_REPAIR_ENABLED','VARCHAR2','N',44,
1040 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1041 1,SYSDATE,'Y','Y','Collect External Repair Orders',p_version);
1042
1043 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1044 NAME,
1045 DATA_TYPE,
1046 DEFAULT_VALUE,
1047 SEQUENCE,
1048 SQL,
1049 CREATED_BY,
1050 CREATION_DATE,
1051 REQUIRED,
1052 DISPLAYED,
1053 DISPLAY_NAME,
1054 PARAM_VERSION)
1055 VALUES(11,'PAYBACK_DEMAND_SUPPLY_ENABLED','VARCHAR2','N',45,
1056 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1057 1,SYSDATE,'Y','Y','Enable Pay Back Demand Supply',p_version);
1058
1059 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1060 NAME,
1061 DATA_TYPE,
1062 DEFAULT_VALUE,
1063 SEQUENCE,
1064 SQL,
1065 CREATED_BY,
1066 CREATION_DATE,
1067 REQUIRED,
1068 DISPLAYED,
1069 DISPLAY_NAME,
1070 PARAM_VERSION)
1071 VALUES(11,'CURRENCY_CONVERSION_ENABLED','VARCHAR2','N',46,
1072 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1073 1,SYSDATE,'Y','Y','Enable Currency Conversion',p_version);
1074
1075 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1076 NAME,
1077 DATA_TYPE,
1078 DEFAULT_VALUE,
1079 SEQUENCE,
1080 SQL,
1081 CREATED_BY,
1082 CREATION_DATE,
1083 REQUIRED,
1084 DISPLAYED,
1085 DISPLAY_NAME,
1086 PARAM_VERSION)
1087 VALUES(11,'DELIVERY_DETAILS_ENABLED','VARCHAR2','N',47,
1088 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1089 1,SYSDATE,'Y','Y','Enable Delivery Details',p_version);
1090
1091
1092
1093 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1094 NAME,
1095 DATA_TYPE,
1096 DEFAULT_VALUE,
1097 SEQUENCE,
1098 SQL,
1099 CREATED_BY,
1100 CREATION_DATE,
1101 REQUIRED,
1102 DISPLAYED,
1103 DISPLAY_NAME,
1104 PARAM_VERSION)
1105 VALUES(11,'ODSTOTALWORKERNUM','NUMBER','3',48,NULL,
1106 1,SYSDATE,'Y','Y','Number of workers',p_version);
1107
1108
1109 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1110 NAME,
1111 DATA_TYPE,
1112 DEFAULT_VALUE,
1113 SEQUENCE,
1114 SQL,
1115 CREATED_BY,
1116 CREATION_DATE,
1117 REQUIRED,
1118 DISPLAYED,
1119 DISPLAY_NAME,
1120 PARAM_VERSION)
1121 VALUES(11,'RECALC_RES_AVAILABILITY','VARCHAR2','Y',49,
1122 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1123 1,SYSDATE,'Y','Y','Recalculate Sourcing History',p_version);
1124
1125 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1126 NAME,
1127 DATA_TYPE,
1128 DEFAULT_VALUE,
1129 SEQUENCE,
1130 SQL,
1131 CREATED_BY,
1132 CREATION_DATE,
1133 REQUIRED,
1134 DISPLAYED,
1135 DISPLAY_NAME,
1136 PARAM_VERSION)
1137 VALUES(11,'PURGE_SOURCING_HISTORY','VARCHAR2','N',50,
1138 ' select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1139 1,SYSDATE,'Y','Y','Purge Sourcing History',p_version);
1140
1141
1142
1143
1144
1145 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1146 NAME,
1147 DATA_TYPE,
1148 DEFAULT_VALUE,
1149 SEQUENCE,
1150 SQL,
1151 CREATED_BY,
1152 CREATION_DATE,
1153 REQUIRED,
1154 DISPLAYED,
1155 DISPLAY_NAME,
1156 PARAM_VERSION)
1157 VALUES(10,'INSTANCE_ID','NUMBER',NULL,1,'select INSTANCE_ID hidden, INSTANCE_CODE Display '||
1158 'FROM MSC_APPS_INSTANCES '||
1159 'where instance_type IN (1,2,4) and enable_flag = 1',
1160 1,SYSDATE,'Y','Y','Instance',p_version);
1161
1162 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1163 NAME,
1164 DATA_TYPE,
1165 DEFAULT_VALUE,
1166 SEQUENCE,
1167 SQL,
1168 CREATED_BY,
1169 CREATION_DATE,
1170 REQUIRED,
1171 DISPLAYED,
1172 DISPLAY_NAME,
1173 PARAM_VERSION)
1174 VALUES(10,'COLLECTION_GROUP','VARCHAR','-999',2,
1175 'select CODE hidden, ORG_GROUP Display FROM MSD_DEM_ORG_GROUPS_V ',
1176 1,SYSDATE,'Y','Y','Shipment and Booking History -Collection Group',p_version);
1177
1178 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1179 NAME,
1180 DATA_TYPE,
1181 DEFAULT_VALUE,
1182 SEQUENCE,
1183 SQL,
1184 CREATED_BY,
1185 CREATION_DATE,
1186 REQUIRED,
1187 DISPLAYED,
1188 DISPLAY_NAME,
1189 PARAM_VERSION)
1190 VALUES(10,'COLLECTION_METHOD','NUMBER',NULL,3,
1191 'select lookup_code,meaning from fnd_lookup_values_vl flv WHERE flv.lookup_type = ''MSD_DEM_COLL_METHODS''',
1192 1,SYSDATE,'Y','Y','Shipment and Booking History -Collection Method',p_version);
1193
1194 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1195 NAME,
1196 DATA_TYPE,
1197 DEFAULT_VALUE,
1198 SEQUENCE,
1199 SQL,
1200 CREATED_BY,
1201 CREATION_DATE,
1202 REQUIRED,
1203 DISPLAYED,
1204 DISPLAY_NAME,
1205 PARAM_VERSION)
1206 VALUES(10,'DATE_RANGE_TYPE','NUMBER',NULL,4,
1207 ' select lookup_code,meaning from fnd_lookup_values_vl where lookup_type = ''MSD_DEM_DATE_RANGE_TYPES''',
1208 1,SYSDATE,'Y','Y','Date Range Type',p_version);
1209
1210 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1211 NAME,
1212 DATA_TYPE,
1213 DEFAULT_VALUE,
1214 SEQUENCE,
1215 SQL,
1216 CREATED_BY,
1217 CREATION_DATE,
1218 REQUIRED,
1219 DISPLAYED,
1220 DISPLAY_NAME,
1221 PARAM_VERSION)
1222 VALUES(10,'HISTORY_COLLECTION_WINDOW','NUMBER',NULL,5,NULL,
1223 1,SYSDATE,'N','Y','Shipment and Booking History - History Collection Window',p_version);
1224
1225 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1226 NAME,
1227 DATA_TYPE,
1228 DEFAULT_VALUE,
1229 SEQUENCE,
1230 SQL,
1231 CREATED_BY,
1232 CREATION_DATE,
1233 REQUIRED,
1234 DISPLAYED,
1235 DISPLAY_NAME,
1236 PARAM_VERSION)
1237 VALUES(10,'DATE_FROM','DATE',NULL,6,NULL,
1238 1,SYSDATE,'N','Y','Shipment and Booking History - Date From',p_version);
1239
1240 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1241 NAME,
1242 DATA_TYPE,
1243 DEFAULT_VALUE,
1244 SEQUENCE,
1245 SQL,
1246 CREATED_BY,
1247 CREATION_DATE,
1248 REQUIRED,
1249 DISPLAYED,
1250 DISPLAY_NAME,
1251 PARAM_VERSION)
1252 VALUES(10,'DATE_TO','DATE',NULL,7,NULL,
1253 1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
1254
1255 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1256 NAME,
1257 DATA_TYPE,
1258 DEFAULT_VALUE,
1259 SEQUENCE,
1260 SQL,
1261 CREATED_BY,
1262 CREATION_DATE,
1263 REQUIRED,
1264 DISPLAYED,
1265 DISPLAY_NAME,
1266 PARAM_VERSION)
1267 VALUES(10,'BH_BOOKED_ITEMS_BOOKED_DATE','VARCHAR','N',8,
1268 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1269 1,SYSDATE,'Y','Y','Booking History - Booked Items - Booked Date',p_version);
1270
1271 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1272 NAME,
1273 DATA_TYPE,
1274 DEFAULT_VALUE,
1275 SEQUENCE,
1276 SQL,
1277 CREATED_BY,
1278 CREATION_DATE,
1279 REQUIRED,
1280 DISPLAYED,
1281 DISPLAY_NAME,
1282 PARAM_VERSION)
1283 VALUES(10,'BH_BOOKED_ITEMS_REQUESTED_DATE','VARCHAR','N',9,
1284 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1285 1,SYSDATE,'Y','Y','Booking History - Booked Items - Requested Date',p_version);
1286
1287 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1288 NAME,
1289 DATA_TYPE,
1290 DEFAULT_VALUE,
1291 SEQUENCE,
1292 SQL,
1293 CREATED_BY,
1294 CREATION_DATE,
1295 REQUIRED,
1296 DISPLAYED,
1297 DISPLAY_NAME,
1298 PARAM_VERSION)
1299 VALUES(10,'BH_REQUESTED_ITEMS_BOOKED_DATE','VARCHAR','N',10,
1300 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1301 1,SYSDATE,'Y','Y','Booking History - Requested Items - Booked Date',p_version);
1302
1303 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1304 NAME,
1305 DATA_TYPE,
1306 DEFAULT_VALUE,
1307 SEQUENCE,
1308 SQL,
1309 CREATED_BY,
1310 CREATION_DATE,
1311 REQUIRED,
1312 DISPLAYED,
1313 DISPLAY_NAME,
1314 PARAM_VERSION)
1315 VALUES(10,'BH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',11,
1316 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1317 1,SYSDATE,'Y','Y','Booking History - Requested Items - Requested Date',p_version);
1318
1319 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1320 NAME,
1321 DATA_TYPE,
1322 DEFAULT_VALUE,
1323 SEQUENCE,
1324 SQL,
1325 CREATED_BY,
1326 CREATION_DATE,
1327 REQUIRED,
1328 DISPLAYED,
1329 DISPLAY_NAME,
1330 PARAM_VERSION)
1331 VALUES(10,'SH_SHIPPED_ITEMS_SHIPPED_DATE','VARCHAR','N',12,
1332 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1333 1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Shipped Date',p_version);
1334
1335 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1336 NAME,
1337 DATA_TYPE,
1338 DEFAULT_VALUE,
1339 SEQUENCE,
1340 SQL,
1341 CREATED_BY,
1342 CREATION_DATE,
1343 REQUIRED,
1344 DISPLAYED,
1345 DISPLAY_NAME,
1346 PARAM_VERSION)
1347 VALUES(10,'SH_SHIPPED_ITEMS_REQUESTED_DATE','VARCHAR','N',13,
1348 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1349 1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Requested Date',p_version);
1350
1351 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1352 NAME,
1353 DATA_TYPE,
1354 DEFAULT_VALUE,
1355 SEQUENCE,
1356 SQL,
1357 CREATED_BY,
1358 CREATION_DATE,
1359 REQUIRED,
1360 DISPLAYED,
1361 DISPLAY_NAME,
1362 PARAM_VERSION)
1363 VALUES(10,'SH_REQUESTED_ITEMS_SHIPPED_DATE','VARCHAR','N',14,
1364 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1365 1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Shipped Date',p_version);
1366
1367 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1368 NAME,
1369 DATA_TYPE,
1370 DEFAULT_VALUE,
1371 SEQUENCE,
1372 SQL,
1373 CREATED_BY,
1374 CREATION_DATE,
1375 REQUIRED,
1376 DISPLAYED,
1377 DISPLAY_NAME,
1378 PARAM_VERSION)
1379 VALUES(10,'SH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',15,
1380 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1381 1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Requested Date',p_version);
1382
1383 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1384 NAME,
1385 DATA_TYPE,
1386 DEFAULT_VALUE,
1387 SEQUENCE,
1388 SQL,
1389 CREATED_BY,
1390 CREATION_DATE,
1391 REQUIRED,
1392 DISPLAYED,
1393 DISPLAY_NAME,
1394 PARAM_VERSION)
1395 VALUES(10,'COLLECT_ISO','VARCHAR','N',16,
1396 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1397 1,SYSDATE,'Y','Y','Collect Internal Sales Orders',p_version);
1398
1399 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1400 NAME,
1401 DATA_TYPE,
1402 DEFAULT_VALUE,
1403 SEQUENCE,
1404 SQL,
1405 CREATED_BY,
1406 CREATION_DATE,
1407 REQUIRED,
1408 DISPLAYED,
1409 DISPLAY_NAME,
1410 PARAM_VERSION)
1411 VALUES(10,'COLLECT_ALL_ORDER_TYPES','VARCHAR','Y',17,
1412 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1413 1,SYSDATE,'Y','Y','Collect All Order Types',p_version);
1414
1415 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1416 NAME,
1417 DATA_TYPE,
1418 DEFAULT_VALUE,
1419 SEQUENCE,
1420 SQL,
1421 CREATED_BY,
1422 CREATION_DATE,
1423 REQUIRED,
1424 DISPLAYED,
1425 DISPLAY_NAME,
1426 PARAM_VERSION)
1427 VALUES(10,'INCLUDE_ORDER_TYPES','VARCHAR','N',18,NULL,1,SYSDATE,'N','Y','Include Order types',p_version);
1428
1429 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1430 NAME,
1431 DATA_TYPE,
1432 DEFAULT_VALUE,
1433 SEQUENCE,
1434 SQL,
1435 CREATED_BY,
1436 CREATION_DATE,
1437 REQUIRED,
1438 DISPLAYED,
1439 DISPLAY_NAME,
1440 PARAM_VERSION)
1441 VALUES(10,'EXCLUDE_ORDER_TYPES','VARCHAR','N',19,NULL,1,SYSDATE,'N','Y','Exclude Order types',p_version);
1442
1443
1444 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1445 NAME,
1446 DATA_TYPE,
1447 DEFAULT_VALUE,
1448 SEQUENCE,
1449 SQL,
1450 CREATED_BY,
1451 CREATION_DATE,
1452 REQUIRED,
1453 DISPLAYED,
1454 DISPLAY_NAME,
1455 PARAM_VERSION)
1456 VALUES(10,'LAUNCH_DOWNLOAD','VARCHAR','N',20,
1457 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1458 1,SYSDATE,'Y','Y','Launch Download',p_version);
1459
1460
1461 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1462 NAME,
1463 DATA_TYPE,
1464 DEFAULT_VALUE,
1465 SEQUENCE,
1466 SQL,
1467 CREATED_BY,
1468 CREATION_DATE,
1469 REQUIRED,
1470 DISPLAYED,
1471 DISPLAY_NAME,
1472 PARAM_VERSION)
1473 VALUES(10,'DATE_FROM3','DATE',NULL,21,NULL,
1474 1,SYSDATE,'N','Y','Currency Conversions - Date From',p_version);
1475
1476 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1477 NAME,
1478 DATA_TYPE,
1479 DEFAULT_VALUE,
1480 SEQUENCE,
1481 SQL,
1482 CREATED_BY,
1483 CREATION_DATE,
1484 REQUIRED,
1485 DISPLAYED,
1486 DISPLAY_NAME,
1487 PARAM_VERSION)
1488 VALUES(10,'DATE_TO3','DATE',NULL,22,NULL,
1489 1,SYSDATE,'N','Y','Currency Conversions - Date To',p_version);
1490
1491 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1492 NAME,
1493 DATA_TYPE,
1494 DEFAULT_VALUE,
1495 SEQUENCE,
1496 SQL,
1497 CREATED_BY,
1498 CREATION_DATE,
1499 REQUIRED,
1500 DISPLAYED,
1501 DISPLAY_NAME,
1502 PARAM_VERSION)
1503 VALUES(10,'COLLECT_ALL_CURRENCIES','VARCHAR','N',23,
1504 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1505 1,SYSDATE,'Y','Y','Collect All Currency Conversions',p_version);
1506
1507 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1508 NAME,
1509 DATA_TYPE,
1510 DEFAULT_VALUE,
1511 SEQUENCE,
1512 SQL,
1513 CREATED_BY,
1514 CREATION_DATE,
1515 REQUIRED,
1516 DISPLAYED,
1517 DISPLAY_NAME,
1518 PARAM_VERSION)
1519 VALUES(10,'INCLUDE_CURRENCY_LIST','VARCHAR',NULL,24,NULL,
1520 1,SYSDATE,'N','Y','Include Currency List',p_version);
1521
1522 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1523 NAME,
1524 DATA_TYPE,
1525 DEFAULT_VALUE,
1526 SEQUENCE,
1527 SQL,
1528 CREATED_BY,
1529 CREATION_DATE,
1530 REQUIRED,
1531 DISPLAYED,
1532 DISPLAY_NAME,
1533 PARAM_VERSION)
1534 VALUES(10,'EXCLUDE_CURRENCY_LIST','VARCHAR',NULL,25,NULL,
1535 1,SYSDATE,'N','Y','Exclude Currency List',p_version);
1536
1537 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1538 NAME,
1539 DATA_TYPE,
1540 DEFAULT_VALUE,
1541 SEQUENCE,
1542 SQL,
1543 CREATED_BY,
1544 CREATION_DATE,
1545 REQUIRED,
1546 DISPLAYED,
1547 DISPLAY_NAME,
1548 PARAM_VERSION)
1549 VALUES(10,'INCLUDE_ALL','VARCHAR','N',26,
1550 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1551 1,SYSDATE,'Y','Y','Collect All Unit of Measures',p_version);
1552
1553 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1554 NAME,
1555 DATA_TYPE,
1556 DEFAULT_VALUE,
1557 SEQUENCE,
1558 SQL,
1559 CREATED_BY,
1560 CREATION_DATE,
1561 REQUIRED,
1562 DISPLAYED,
1563 DISPLAY_NAME,
1564 PARAM_VERSION)
1565 VALUES(10,'INCLUDE_UOM_LIST','VARCHAR',NULL,27,NULL,
1566 1,SYSDATE,'N','Y','Include Unit of Measures',p_version);
1567
1568 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1569 NAME,
1570 DATA_TYPE,
1571 DEFAULT_VALUE,
1572 SEQUENCE,
1573 SQL,
1574 CREATED_BY,
1575 CREATION_DATE,
1576 REQUIRED,
1577 DISPLAYED,
1578 DISPLAY_NAME,
1579 PARAM_VERSION)
1580 VALUES(10,'EXCLUDE_UOM_LIST','VARCHAR',NULL,28,NULL,
1581 1,SYSDATE,'N','Y','Exclude Unit of Measures',p_version);
1582
1583 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1584 NAME,
1585 DATA_TYPE,
1586 DEFAULT_VALUE,
1587 SEQUENCE,
1588 SQL,
1589 CREATED_BY,
1590 CREATION_DATE,
1591 REQUIRED,
1592 DISPLAYED,
1593 DISPLAY_NAME,
1594 PARAM_VERSION)
1595 VALUES(10,'START_DATE','DATE',NULL,29,NULL,
1596 1,SYSDATE,'Y','Y','Pricing Data - Date From',p_version);
1597
1598 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1599 NAME,
1600 DATA_TYPE,
1601 DEFAULT_VALUE,
1602 SEQUENCE,
1603 SQL,
1604 CREATED_BY,
1605 CREATION_DATE,
1606 REQUIRED,
1607 DISPLAYED,
1608 DISPLAY_NAME,
1609 PARAM_VERSION)
1610 VALUES(10,'END_DATE','DATE',NULL,30,NULL,
1611 1,SYSDATE,'Y','Y','Pricing Data - Date To',p_version);
1612
1613 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1614 NAME,
1615 DATA_TYPE,
1616 DEFAULT_VALUE,
1617 SEQUENCE,
1618 SQL,
1619 CREATED_BY,
1620 CREATION_DATE,
1621 REQUIRED,
1622 DISPLAYED,
1623 DISPLAY_NAME,
1624 PARAM_VERSION)
1625 VALUES(10,'INCLUDE_ALL1','VARCHAR','N',31,
1626 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1627 1,SYSDATE,'Y','Y','Collect all Price Lists',p_version);
1628
1629
1630 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1631 NAME,
1632 DATA_TYPE,
1633 DEFAULT_VALUE,
1634 SEQUENCE,
1635 SQL,
1636 CREATED_BY,
1637 CREATION_DATE,
1638 REQUIRED,
1639 DISPLAYED,
1640 DISPLAY_NAME,
1641 PARAM_VERSION)
1642 VALUES(10,'INCLUDE_PRICE_LIST','VARCHAR',NULL,32,NULL,
1643 1,SYSDATE,'N','Y','Include Price Lists',p_version);
1644
1645 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1646 NAME,
1647 DATA_TYPE,
1648 DEFAULT_VALUE,
1649 SEQUENCE,
1650 SQL,
1651 CREATED_BY,
1652 CREATION_DATE,
1653 REQUIRED,
1654 DISPLAYED,
1655 DISPLAY_NAME,
1656 PARAM_VERSION)
1657 VALUES(10,'EXCLUDE_PRICE_LIST','VARCHAR',NULL,33,NULL,
1658 1,SYSDATE,'N','Y','Exclude Price Lists',p_version);
1659
1660 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1661 NAME,
1662 DATA_TYPE,
1663 DEFAULT_VALUE,
1664 SEQUENCE,
1665 SQL,
1666 CREATED_BY,
1667 CREATION_DATE,
1668 REQUIRED,
1669 DISPLAYED,
1670 DISPLAY_NAME,
1671 PARAM_VERSION)
1672 VALUES(10,'COLLECTION_GROUP1','VARCHAR','-999',34,
1673 'select CODE hidden, ORG_GROUP Display FROM MSD_DEM_ORG_GROUPS_V ',
1674 1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Group',p_version);
1675
1676 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1677 NAME,
1678 DATA_TYPE,
1679 DEFAULT_VALUE,
1680 SEQUENCE,
1681 SQL,
1682 CREATED_BY,
1683 CREATION_DATE,
1684 REQUIRED,
1685 DISPLAYED,
1686 DISPLAY_NAME,
1687 PARAM_VERSION)
1688 VALUES(10,'COLLECTION_METHOD1','NUMBER',NULL,35,
1689 'select lookup_code,meaning from fnd_lookup_values_vl flv WHERE flv.lookup_type = ''MSD_DEM_COLL_METHODS''',
1690 1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Method',p_version);
1691
1692 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1693 NAME,
1694 DATA_TYPE,
1695 DEFAULT_VALUE,
1696 SEQUENCE,
1697 SQL,
1698 CREATED_BY,
1699 CREATION_DATE,
1700 REQUIRED,
1701 DISPLAYED,
1702 DISPLAY_NAME,
1703 PARAM_VERSION)
1704 VALUES(10,'DATE_RANGE_TYPE1','NUMBER',NULL,36,
1705 'select lookup_code,meaning from fnd_lookup_values_vl where lookup_type = ''MSD_DEM_DATE_RANGE_TYPES''',
1706 1,SYSDATE,'Y','Y','Date Range Type',p_version);
1707
1708
1709 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1710 NAME,
1711 DATA_TYPE,
1712 DEFAULT_VALUE,
1713 SEQUENCE,
1714 SQL,
1715 CREATED_BY,
1716 CREATION_DATE,
1717 REQUIRED,
1718 DISPLAYED,
1719 DISPLAY_NAME,
1720 PARAM_VERSION)
1721 VALUES(10,'HISTORY_COLLECTION_WINDOW1','NUMBER',NULL,37,NULL,
1722 1,SYSDATE,'N','Y','Supply Chain Intelligence Data - History Collection Window',p_version);
1723
1724
1725
1726 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1727 NAME,
1728 DATA_TYPE,
1729 DEFAULT_VALUE,
1730 SEQUENCE,
1731 SQL,
1732 CREATED_BY,
1733 CREATION_DATE,
1734 REQUIRED,
1735 DISPLAYED,
1736 DISPLAY_NAME,
1737 PARAM_VERSION)
1738 VALUES(10,'DATE_FROM1','DATE',NULL,38,NULL,
1739 1,SYSDATE,'N','Y','Supply Chain Intelligence Data - Date From',p_version);
1740
1741
1742 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1743 NAME,
1744 DATA_TYPE,
1745 DEFAULT_VALUE,
1746 SEQUENCE,
1747 SQL,
1748 CREATED_BY,
1749 CREATION_DATE,
1750 REQUIRED,
1751 DISPLAYED,
1752 DISPLAY_NAME,
1753 PARAM_VERSION)
1754 VALUES(10,'DATE_TO1','DATE',NULL,39,NULL,
1755 1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
1756
1757
1758 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1759 NAME,
1760 DATA_TYPE,
1761 DEFAULT_VALUE,
1762 SEQUENCE,
1763 SQL,
1764 CREATED_BY,
1765 CREATION_DATE,
1766 REQUIRED,
1767 DISPLAYED,
1768 DISPLAY_NAME,
1769 PARAM_VERSION)
1770 VALUES(13,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1771 1,SYSDATE,'Y','Y','New Plan Name',p_version);
1772
1773 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1774 NAME,
1775 DATA_TYPE,
1776 DEFAULT_VALUE,
1777 SEQUENCE,
1778 SQL,
1779 CREATED_BY,
1780 CREATION_DATE,
1781 REQUIRED,
1782 DISPLAYED,
1783 DISPLAY_NAME,
1784 PARAM_VERSION)
1785 VALUES(13,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
1786 'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
1787 'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
1788 'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
1789 'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1790 1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
1791
1792 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1793 NAME,
1794 DATA_TYPE,
1795 DEFAULT_VALUE,
1796 SEQUENCE,
1797 SQL,
1798 CREATED_BY,
1799 CREATION_DATE,
1800 REQUIRED,
1801 DISPLAYED,
1802 DISPLAY_NAME,
1803 PARAM_VERSION)
1804 VALUES(13,'ARCHIVE_FLAG','NUMBER',2,3,
1805 'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1806 1,sysdate,'Y','Y','Archive Forecast',p_version);
1807
1808 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1809 NAME,
1810 DATA_TYPE,
1811 DEFAULT_VALUE,
1812 SEQUENCE,
1813 SQL,
1814 CREATED_BY,
1815 CREATION_DATE,
1816 REQUIRED,
1817 DISPLAYED,
1818 DISPLAY_NAME,
1819 PARAM_VERSION)
1820 VALUES(19,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1821 1,SYSDATE,'Y','Y','New Plan Name',p_version);
1822
1823 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1824 NAME,
1825 DATA_TYPE,
1826 DEFAULT_VALUE,
1827 SEQUENCE,
1828 SQL,
1829 CREATED_BY,
1830 CREATION_DATE,
1831 REQUIRED,
1832 DISPLAYED,
1833 DISPLAY_NAME,
1834 PARAM_VERSION)
1835 VALUES(19,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
1836 'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
1837 'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
1838 'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
1839 'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1840 1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
1841
1842 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1843 NAME,
1844 DATA_TYPE,
1845 DEFAULT_VALUE,
1846 SEQUENCE,
1847 SQL,
1848 CREATED_BY,
1849 CREATION_DATE,
1850 REQUIRED,
1851 DISPLAYED,
1852 DISPLAY_NAME,
1853 PARAM_VERSION)
1854 VALUES(19,'ARCHIVE_FLAG','NUMBER',2,3,
1855 'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1856 1,sysdate,'Y','Y','Archive Forecast',p_version);
1857
1858 commit;
1859
1860 end if;
1861
1862 exception
1863 when others then
1864 commit;
1865 end insert_seed_data;
1866
1867 FUNCTION get_plan_name(p_plan_type in number,p_plan_id in number, p_plan_run_id in number) return varchar2 is
1868
1869 cursor c_plan_name is
1870 select compile_designator as plan_name
1871 from msc_plans
1872 where plan_type = nvl(p_plan_type,plan_type) and plan_id = p_plan_id
1873 union
1874 select distinct scenario_name as plan_name
1875 from msd_dp_ascp_scenarios_v
1876 where scenario_id=p_plan_id and p_plan_type=10;
1877
1878 cursor c_plan_name_arch is
1879 select plan_run_name
1880 from msc_plan_runs
1881 where plan_id=p_plan_id and
1882 plan_run_id = p_plan_run_id;
1883
1884 cursor c_end_date is
1885 select end_date
1886 from msc_plan_runs
1887 where plan_run_id = p_plan_run_id;
1888
1889 l_plan_name varchar2(80);
1890 l_plan_name_arch varchar2(80);
1891 l_end_date date;
1892 begin
1893 if p_plan_run_id is null then
1894 open c_plan_name;
1895 fetch c_plan_name into l_plan_name;
1896 close c_plan_name;
1897 else
1898 open c_plan_name_arch;
1899 fetch c_plan_name_arch into l_plan_name_arch;
1900 close c_plan_name_arch;
1901 end if;
1902
1903 open c_end_date;
1904 fetch c_end_date into l_end_date;
1905 close c_end_date;
1906
1907 -- l_plan_name := l_plan_name||to_char(l_end_date);
1908
1909 if p_plan_run_id is not null and l_plan_name_arch is not null then
1910 return l_plan_name_arch;
1911 else
1912 return l_plan_name;
1913 end if;
1914 end;
1915
1916 FUNCTION get_owner_name(p_owner_id in number) return varchar2 is
1917 cursor c_owner_name is
1918 select user_name
1919 from fnd_user
1920 where user_id = p_owner_id;
1921
1922 l_user_name varchar2(80);
1923 begin
1924 open c_owner_name;
1925 fetch c_owner_name into l_user_name;
1926 close c_owner_name;
1927
1928 return l_user_name;
1929 end;
1930
1931 FUNCTION get_scn_version(p_version date) return varchar2 is
1932 l_scn_version varchar2(30);
1933 begin
1934 if p_version is null then
1935 l_scn_version := 'Current';
1936 else
1937 l_scn_version := to_char(p_version);
1938 end if;
1939
1940 return l_scn_version;
1941 end;
1942
1943 FUNCTION get_plan_version(p_plan_run_id in number) return varchar2 is
1944 cursor c_end_date is
1945 select end_date
1946 from msc_plan_runs
1947 where plan_run_id = p_plan_run_id;
1948
1949 l_pln_version varchar2(30);
1950 l_end_date date;
1951 begin
1952 if p_plan_run_id is null or p_plan_run_id = -1 then
1953 l_pln_version := 'Current';
1954 else
1955 open c_end_date;
1956 fetch c_end_date into l_end_date;
1957 close c_end_date;
1958 l_pln_version := to_char(l_end_date);
1959 end if;
1960
1961 return l_pln_version;
1962 end;
1963
1964 FUNCTION get_scenario_name(p_scenario_id in number) return varchar2 is
1965 cursor c_scn_name is
1966 select scenario_name
1967 from msc_scenarios
1968 where scenario_id = p_scenario_id;
1969
1970 l_scn_name varchar2(100);
1971 begin
1972 open c_scn_name;
1973 fetch c_scn_name into l_scn_name;
1974 close c_scn_name;
1975
1976 return l_scn_name;
1977 end;
1978
1979 FUNCTION get_proc_act_name(p_activity_type in number) return varchar2 is
1980 cursor c_act_name is
1981 select meaning from mfg_lookups
1982 where lookup_type = 'MSC_PROCESS_ACTIVITY_TYPES'
1983 and lookup_code = p_activity_type;
1984
1985 l_act_name varchar2(100);
1986 begin
1987 open c_act_name;
1988 fetch c_act_name into l_act_name;
1989 close c_act_name;
1990
1991 return l_act_name;
1992 end;
1993
1994 procedure copy_scn_plans(p_src_scnId in number, p_dest_scnId in number) is
1995 l_user_id number;
1996 l_login_id number;
1997 begin
1998 l_user_id := fnd_profile.value ('USER_ID');
1999 l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2000 if(p_src_scnId is not null and p_dest_scnId is not null) then
2001
2002 insert into msc_scenario_plans (scenario_id,
2003 plan_type,
2004 plan_id,
2005 created_by,
2006 creation_Date ,
2007 last_update_date,
2008 last_updated_by,
2009 last_update_login,
2010 status,
2011 run_date,
2012 plan_horizon,
2013 plan_run_id)
2014 (select p_dest_scnId,
2015 plan_type,
2016 plan_id,
2017 l_user_id,
2018 sysdate,
2019 sysdate,
2020 l_user_id,
2021 l_login_id,
2022 status,
2023 run_date,
2024 plan_horizon,
2025 plan_run_id from msc_scenario_plans where scenario_id = p_src_scnId);
2026 end if;
2027 end copy_scn_plans;
2028
2029 FUNCTION get_scn_users (p_scn_id in number) return varchar2 is
2030 cursor c_scn_users is
2031 select msc_scn_utils.get_owner_name(user_id)
2032 from msc_scenario_users
2033 where scenario_id = p_scn_id;
2034
2035 l_scn_users varchar2(2000) := '';
2036 l_scn_user varchar2(100);
2037
2038 begin
2039 open c_scn_users;
2040 loop
2041 fetch c_scn_users into l_scn_user;
2042 EXIT WHEN c_scn_users%NOTFOUND;
2043 l_scn_users := l_scn_users||l_scn_user||',';
2044 end loop;
2045 close c_scn_users;
2046 l_scn_users := l_scn_users||l_scn_user;
2047
2048 return l_scn_users;
2049 end;
2050
2051 /* count of scenarios that has this plan*/
2052 function plan_scns_count(p_plan_id in number, p_scn_id in number, p_plan_run_id in number) return number is
2053 l_count number := 0;
2054
2055 cursor c_plans is
2056 select count(*) from msc_scenario_plans
2057 where scenario_id <> p_scn_id and plan_id = p_plan_id and plan_run_id = p_plan_run_id;
2058
2059 begin
2060 open c_plans;
2061 fetch c_plans into l_count;
2062 close c_plans;
2063
2064 return l_count;
2065 end;
2066
2067 /* This procedure is called to archive a scenario.
2068 Logic
2069 Archive_Scn_Conc looks at all the plans to be archived (Archive_flag in msc_scenario_plans) and
2070 for every plan_id,
2071 generate plan_run_id and update plan_run_id field for it.
2072 call populate_Details api
2073 end for;
2074 insert new record into msc_scenarios for current version of the scenario
2075 update scenario_name,version for this scenario_id by appending sysdate to it
2076 */
2077
2078
2079 procedure archive_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2080
2081 cursor c_scn_plans is
2082 select plan_type,plan_id,run_date,plan_run_id
2083 from msc_scenario_plans
2084 where scenario_id = p_scn_id and archive_flag = 'Y';
2085
2086 l_plan_type number;
2087 l_plan_id number;
2088 l_run_date date;
2089
2090 l_old_plan_run_id number;
2091 l_publisher varchar2(200) := null;
2092 l_plan_name varchar2(50) := null;
2093 l_plan_run_id number;
2094 l_scn_name varchar2(50);
2095 l_err_buf varchar2(240);
2096 l_user_id number;
2097 l_login_id number;
2098 l_new_scn_id number;
2099 l_scn_count number :=0;
2100
2101 exc_error_plan_arch EXCEPTION;
2102 begin
2103 if p_scn_id is null then
2104 return;
2105 end if;
2106
2107 l_scn_name := msc_scn_utils.get_scenario_name(p_scn_id);
2108 select count(*) into l_scn_count from msc_scenarios where scenario_name like l_scn_name||' ('||sysdate||')%';
2109 l_scn_count := l_scn_count+1;
2110 msc_util.msc_debug('New Scenario Name:'||l_scn_name||' ('||sysdate||')('||l_scn_count||')');
2111 update msc_scenarios set scn_version = sysdate,scenario_name = scenario_name||' ('||sysdate||')('||l_scn_count||')' where scenario_id = p_scn_id;
2112
2113 l_user_id := fnd_profile.value ('USER_ID');
2114 l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2115
2116 select msc_scn_scenarios_s.nextval into l_new_scn_id from dual;
2117
2118 -- Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2119
2120 insert into msc_scenarios(scenario_id,
2121 scenario_name,
2122 created_by,
2123 creation_date,
2124 last_update_date,
2125 last_updated_by,
2126 last_update_login,
2127 parent_scn_id,
2128 description,
2129 owner,
2130 scn_access,
2131 scn_comment,
2132 valid_from,
2133 valid_to,
2134 scn_version,
2135 wc_flag,
2136 gs_name,
2137 gs_name_orig)
2138 (select l_new_scn_id,
2139 l_scn_name,
2140 l_user_id,
2141 sysdate,
2142 sysdate,
2143 l_user_id,
2144 l_login_id,
2145 parent_scn_id,
2146 description,
2147 owner,
2148 scn_access,
2149 scn_comment,
2150 valid_from,
2151 valid_to,
2152 null,
2153 wc_flag,
2154 gs_name,
2155 gs_name_orig
2156 from msc_scenarios where scenario_id = p_scn_id);
2157
2158 insert into msc_scenario_plans (scenario_id,
2159 plan_type,
2160 plan_id,
2161 created_by,
2162 creation_Date ,
2163 last_update_date,
2164 last_updated_by,
2165 last_update_login,
2166 status,
2167 run_date,
2168 plan_horizon,
2169 plan_run_id)
2170 (select l_new_scn_id,
2171 plan_type,
2172 plan_id,
2173 l_user_id,
2174 sysdate,
2175 sysdate,
2176 l_user_id,
2177 l_login_id,
2178 status,
2179 run_date,
2180 plan_horizon,
2181 plan_run_id from msc_scenario_plans where scenario_id = p_scn_id);
2182
2183 insert into msc_scenario_users (scenario_id,
2184 user_id,
2185 created_by,
2186 creation_Date,
2187 last_update_date,
2188 last_updated_by,
2189 last_update_login)
2190 (select l_new_scn_id,
2191 user_id,
2192 l_user_id,
2193 sysdate,
2194 sysdate,
2195 l_user_id,
2196 l_login_id from msc_scenario_users where scenario_id = p_scn_id);
2197 commit;
2198
2199 open c_scn_plans;
2200 loop
2201 fetch c_scn_plans into l_plan_type,l_plan_id,l_run_date,l_old_plan_run_id;
2202 exit when c_scn_plans%NOTFOUND;
2203
2204 l_plan_name := null;
2205 l_publisher := null;
2206 begin
2207 select plan_name, publisher
2208 into l_plan_name, l_publisher
2209 from msc_plan_runs
2210 where plan_run_id=l_old_plan_run_id;
2211 exception when others then null;
2212 end;
2213
2214 l_plan_run_id := msc_phub_pkg.populate_details_fn(errbuf, retcode, l_plan_id,
2215 p_plan_type=>l_plan_type, p_target_plan_name=>l_plan_name, p_publisher=>l_publisher);
2216
2217 if retcode <> 0 then
2218 --null; -- errored out
2219 msc_util.msc_debug('Archive Plan Failed for Plan:'||get_plan_name(l_plan_type,l_plan_id,l_plan_run_id));
2220 raise exc_error_plan_arch;
2221 end if;
2222 update msc_scenario_plans set plan_run_id = l_plan_run_id where
2223 scenario_id = p_scn_id and plan_id = l_plan_id and plan_type = l_plan_type;
2224
2225 commit;
2226
2227 end loop;
2228 close c_scn_plans;
2229
2230 EXCEPTION
2231 when exc_error_plan_arch then
2232 if c_scn_plans%isopen then
2233 Close c_scn_plans;
2234 end if;
2235
2236 retcode := 2;
2237
2238 when OTHERS THEN
2239 retcode := 2;
2240 errbuf := sqlerrm;
2241
2242 end archive_scn_conc;
2243
2244 procedure purge_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2245
2246 cursor c_scn_plans is
2247 select r.plan_run_name, r.plan_run_id
2248 from msc_scenario_plans sp, msc_plan_runs r
2249 where sp.scenario_id = p_scn_id and sp.purge_flag = 'Y'
2250 and sp.plan_run_id = r.plan_run_id;
2251
2252 cursor c_scn_version is
2253 select scn_version
2254 from msc_scenarios
2255 where scenario_id = p_scn_id;
2256
2257 l_scn_version date;
2258 l_plan_run_name varchar2(50);
2259 l_plan_run_id number;
2260 exc_error_purge_scn_pln EXCEPTION;
2261 begin
2262 open c_scn_version;
2263 fetch c_scn_version into l_scn_version;
2264 close c_scn_version;
2265
2266 if l_scn_version is not null then
2267 open c_scn_plans;
2268
2269 loop
2270 fetch c_scn_plans into l_plan_run_name, l_plan_run_id;
2271 exit when c_scn_plans%NOTFOUND;
2272
2273 msc_phub_file_pkg.purge_plan_summary(errbuf,retcode,null,l_plan_run_id);
2274 if retcode <> 0 then
2275 msc_util.msc_debug('Purge Plan Failed for Plan:'||l_plan_run_name);
2276 raise exc_error_purge_scn_pln;
2277 end if;
2278
2279 end loop;
2280 close c_scn_plans;
2281
2282 end if;
2283 delete msc_scenarios where scenario_id = p_scn_id;
2284
2285 delete msc_scenario_plans where scenario_id = p_scn_id;
2286
2287 delete msc_Scenario_users where scenario_id = p_scn_id;
2288
2289 delete msc_Scenario_set_details where scenario_id = p_scn_id;
2290
2291 commit;
2292 EXCEPTION
2293 when exc_error_purge_scn_pln then
2294 if c_scn_plans%isopen then
2295 Close c_scn_plans;
2296 end if;
2297 retcode := 2;
2298
2299 when OTHERS THEN
2300 retcode := 2;
2301 errbuf := sqlerrm;
2302
2303 end purge_scn_conc;
2304
2305 procedure purge_plan_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_plan_name varchar2) is
2306 exc_error_purge_plan EXCEPTION;
2307 l_plan_name varchar2(50);
2308 l_plan_run_id number;
2309 begin
2310 begin
2311 select plan_name, plan_run_id
2312 into l_plan_name, l_plan_run_id
2313 from msc_plan_runs
2314 where plan_run_name = p_plan_name;
2315 exception
2316 when no_data_found then
2317 l_plan_name := p_plan_name;
2318 end;
2319
2320 if (l_plan_name is not null) then
2321 msc_phub_file_pkg.purge_plan_summary(errbuf, retcode, l_plan_name, l_plan_run_id);
2322 if retcode <> 0 then
2323 --null; -- errored out
2324 raise exc_error_purge_plan;
2325 end if;
2326
2327 delete from msc_scenario_plans where plan_run_id in (select plan_run_id from msc_plan_runs where plan_name=l_plan_name);
2328 commit;
2329 end if;
2330 EXCEPTION
2331 when exc_error_purge_plan then
2332 retcode := 2;
2333
2334 when OTHERS THEN
2335 retcode := 2;
2336 errbuf := sqlerrm;
2337
2338 end purge_plan_conc;
2339
2340
2341
2342 /*==================================
2343 query_id --> Activity_type
2344 char1 --> param_name
2345 char2 ---> hidden value
2346 char3 ---> display_value
2347
2348 ====================================*/
2349 procedure populate_act_params_for_lov(p_activity_type in number) is
2350
2351 cursor c_activity_params is
2352 select name,sql
2353 from msc_activity_parameters
2354 where activity_type = p_activity_type and sql is not null;
2355
2356 l_count number :=0;
2357 l_param_name varchar2(80);
2358 l_sql varchar2(2000);
2359 l_substr varchar2(2000);
2360 l_insert varchar2(2000);
2361 l_select varchar2(2000);
2362 l_sql_stmnt varchar2(3000);
2363 begin
2364 IF p_activity_type = -99 THEN
2365 insert_seed_data;
2366 RETURN;
2367 END IF;
2368
2369 select count(*) into l_count from msc_form_query where query_id = p_activity_type;
2370
2371 if nvl(l_count,0) >0 then
2372 return;
2373 end if;
2374
2375 open c_activity_params;
2376 loop
2377 fetch c_activity_params into l_param_name,l_sql;
2378 exit when c_activity_params%NOTFOUND;
2379
2380 l_substr := substr(l_sql,instr(upper(l_sql),'SELECT ')+7);
2381 l_insert := 'insert into msc_form_query (query_id,
2382 char1,
2383 last_update_date,
2384 last_updated_by,
2385 last_update_login,
2386 creation_date,
2387 created_by,
2388 char2,
2389 char3)';
2390 l_select := ' select '||p_activity_type||','||''''||l_param_name||''''||','||'sysdate,1,1,sysdate,1,'||l_substr;
2391
2392 l_sql_stmnt := l_insert||l_select;
2393
2394 commit;
2395 msc_get_name.execute_dsql(l_sql_stmnt);
2396
2397 commit;
2398 end loop;
2399 close c_activity_params;
2400 end populate_act_params_for_lov;
2401
2402 FUNCTION Scenario_Status(p_Scenario_id in number) return varchar2 is
2403 begin
2404 /*
2405 This function returns the status of a scenario based on both
2406 manual activities and plans in that scenario.
2407
2408 We will look into two tables i.e
2409 MSC_SCENARIO_ACTIVITIES
2410 MSC_SCENARIO_PLANS
2411 Logic for deriving Status is as follows:
2412
2413 Scenario status When this condition is satisfied
2414 --------------- --------------------------------
2415 Not started When all activities (user and system) are not started
2416 In Progress When atleast 1 activity (user or system) is in progress
2417 Completed When all activities (user and system) are complete
2418 Error When atleast 1 activity (user or system) is in error state
2419 Warning When atleast 1 activity (user or system) is in warning state
2420
2421
2422 MSC_SCN_PLAN_STATUS 1 Completed
2423 MSC_SCN_PLAN_STATUS 2 Error
2424 MSC_SCN_PLAN_STATUS 3 Warning
2425 MSC_SCN_PLAN_STATUS 4 In Progress
2426 */
2427 return to_char(null);
2428 end Scenario_Status;
2429
2430 FUNCTION plan_Status(p_Plan_id in number) return varchar2 is
2431 cursor c_plan_status(c_plan_id number) is
2432 Select
2433 msc_Get_name.lookup_meaning(
2434 'MSC_SCN_PLAN_STATUS',
2435 decode(upper(fcr.status_code),'C',4,'E',3)
2436 ) plan_status_display
2437 from msc_plans mp,
2438 msc_plan_runs mpr,
2439 fnd_concurrent_requests fcr
2440 where mp.plan_type is not null and
2441 mp.plan_id = mpr.plan_id and
2442 mpr.end_date is not null and
2443 mp.plan_completion_date is not null and
2444 mp.request_id = fcr.request_id
2445 and mp.plan_id = c_plan_id;
2446 l_status varchar2(100) :=null;
2447
2448 begin
2449 if p_plan_id is null then
2450 return to_char(null);
2451 end if;
2452 open c_plan_Status(p_plan_id);
2453 fetch c_plan_status into l_status;
2454 close c_plan_status;
2455 return to_char(l_status);
2456 end;
2457
2458
2459 FUNCTION get_scenario_set_name(p_scenario_set_id in number) return varchar2 is
2460 cursor c_scn_set_name is
2461 select scenario_set_name
2462 from msc_scenario_sets
2463 where scenario_set_id = p_scenario_set_id;
2464
2465 l_scn_set_name varchar2(100);
2466 begin
2467 open c_scn_set_name;
2468 fetch c_scn_set_name into l_scn_set_name;
2469 close c_scn_set_name;
2470
2471 return l_scn_set_name;
2472 end;
2473
2474 procedure get_Activity_Summary(where_clause IN OUT NOCOPY varchar2, activity_summary In out NOCOPY varchar2) is
2475 TYPE DynaCurTyp IS REF CURSOR;
2476 act_cv DynaCurTyp;
2477 sql_stmt VARCHAR2(32000) := null;
2478 select_query varchar2(32000) := null;
2479 from_query varchar2(32000) := null;
2480 l_where_clause varchar2(32000) := null;
2481 group_by varchar2(32000) :=null;
2482
2483 source_table varchar2(1000);
2484 activity_status varchar2(1000);
2485 ppf varchar2(1000);
2486 status number;
2487 summ number;
2488
2489 act1 number :=0;
2490 act2 number :=0;
2491 act3 number :=0;
2492 act4 number :=0;
2493 act5 number :=0;
2494 act6 number :=0;
2495 act7 number :=0;
2496 act8 number :=0;
2497 act9 number :=0;
2498 act10 number :=0;
2499 act11 number :=0;
2500 act12 number :=0;
2501 act13 number :=0;
2502 act14 number :=0;
2503 act15 number :=0;
2504 act16 number :=0;
2505 act17 number :=0;
2506 act18 number :=0;
2507 act19 number :=0;
2508 act20 number :=0;
2509 act21 number :=0;
2510 act22 number :=0;
2511 act23 number :=0;
2512 act24 number :=0;
2513 act25 number :=0;
2514 act26 number :=0;
2515 act27 number :=0;
2516 act28 number :=0;
2517 act29 number :=0;
2518 act30 number :=0;
2519 act31 number :=0;
2520 act32 number :=0;
2521 act33 number :=0;
2522 act34 number :=0;
2523 act35 number :=0;
2524 act36 number :=0;
2525 tm_not_started number :=0;
2526 sm_not_started number :=0;
2527 tm_in_progress number :=0;
2528 sm_in_progress number :=0;
2529 tm_error number :=0;
2530 sm_error number :=0;
2531 act_summary varchar2(32000):=null;
2532
2533 begin
2534 act_summary := act1||','||act2||','||act3||','||act4||','||act5||','||act6||','||act7||','||act8||','||act9||','||act10
2535 ||','||act11||','||act12||','||act13||','||act14||','||act15||','||act16||','||act17||','||act18
2536 ||','||act19||','||act20||','||act21||','||act22||','||act23||','||act24||','||act25||','||act26
2537 ||','||act27||','||act28||','||act29||','||act30||','||act31||','||act32||','||act33||','||act34
2538 ||','||act35||','||act36;
2539 -- select all columns in vo as required
2540 select_query := ' SELECT source_table, msc_get_name.lookup_meaning(''MSC_SCN_ACTIVITY_STATES'', status) activity_status, ' ;
2541 select_query:= select_query || ' decode(SIGN(finish_by -TRUNC(sysdate) + 0), -1, ''PAST_DUE'', 0, ''CURRENT'', 1, ''FUTURE'') ppf, ';
2542 select_query:= select_query || ' status, COUNT(activity_id) summ ';
2543 from_query := ' from(select Activity_Name, Activity_Description, Activity_Status, Owner_Name, Finish_By, Activity_Type, Activity_Comment, Scenario_Set_Name, Scenario_Set_Description, Scenario_Name, Scenario_Description, ';
2544 from_query := from_query || ' Scenario_Status, Scenario_Owner_Name, Scenario_Comment, Plan_Name, Plan_Status, Priority_Text, Completed_On, Alternate_Owner_Name, Created_By_User, Creation_Date, Row_Id, Activity_Id, Scenario_Id, ';
2545 from_query := from_query || ' Scenario_set_Id, source_table, Owner, Status, created_by, last_update_date, Last_updated_by, last_update_login, Priority, Alternate_Owner, Scenario_owner from ( (select ';
2546 from_query := from_query || ' msa.Activity_Name Activity_Name, msa.Description Activity_Description, msc_get_name.lookup_meaning(''MSC_SCN_ACTIVITY_STATES'',msa.Status) Activity_Status, msc_pers_queries.get_user(msa.owner) owner_name, ';
2547 from_query := from_query || ' trunc(msa.Finish_By) Finish_By, ''Manual'' activity_type, msa.Act_Comment Activity_Comment, mss.scenario_set_name Scenario_Set_Name, mss.Description Scenario_Set_Description, ms.scenario_name Scenario_Name, ';
2548 from_query := from_query || ' ms.Description Scenario_Description, msc_scn_utils.Scenario_Status(msa.scenario_id) Scenario_Status, msc_pers_queries.get_user(ms.owner) Scenario_Owner_Name, ms.Scn_Comment Scenario_Comment, to_char(Null) Plan_Name, ';
2549 from_query := from_query || ' to_char(NULL) Plan_Status, msc_get_name.lookup_meaning(''MSC_SCN_PRIORITIES'',msa.Priority) Priority_Text, msa.Completed_On Completed_On, msc_pers_queries.get_user(msa.alternate_owner) alternate_owner_name, ';
2550 from_query := from_query || ' msc_pers_queries.get_user(msa.created_by) Created_By_User, msa.Creation_date, msa.rowid ROW_ID, msa.Activity_Id, msa.Scenario_Id, msa.Scenario_set_Id, ''MSA'' source_table, ';
2551 from_query := from_query || ' msa.Owner, msa.Status Status, msa.created_by, msa.last_update_date, msa.Last_updated_by, msa.last_update_login, msa.Priority, msa.Alternate_Owner, ms.owner Scenario_Owner FROM MSC_SCENARIO_ACTIVITIES MSA, ';
2552 from_query := from_query || ' MSC_SCENARIOS MS, MSC_SCENARIO_SETS MSS WHERE ';
2553 from_query := from_query || ' MSA.SCENARIO_ID=MS.SCENARIO_ID(+) AND MSA.SCENARIO_SET_ID=MSS.SCENARIO_SET_ID(+) and status in (1,2,4)) union ';
2554 from_query := from_query || ' (select concat(concat(mpp.process_name,'' - ''),msc_get_name.lookup_meaning(''MSC_PROCESS_ACTIVITY_TYPES'',MPPA.Activity_Type)) Activity_Name, to_char(NULL) Activity_Description, ';
2555 from_query := from_query || ' msc_get_name.lookup_meaning(''MSC_SCN_ACTIVITY_STATES'',MPPA.Status) Activity_Status, msc_pers_queries.get_user(MPPA.owner) Owner_Name, trunc(mppa.creation_date + nvl(time_out,0)) ';
2556 from_query := from_query || ' Finish_By, to_char(decode(MPPA.activity_type,14,''Manual'',15,''Manual'',16,''Manual'',17,''Manual'',18,''Manual'',20,''Manual'',22,''Manual'',23,''Manual'',''System'' ) ) Activity_Type, ';
2557 from_query := from_query || ' to_char(NULL) Activity_Comment, to_char(NULL) Scenario_Set_Name, to_CHAR(NULL) Scenario_Set_Description, to_char(NULL) Scenario_Name, ';
2558 from_query := from_query || ' to_CHAR(NULL) Scenario_Description, to_char(NULL) Scenario_Status, to_CHAR(NULL) Scenario_Owner_Name, to_CHAR(NULL) Scenario_Comment, msc_scn_utils.get_plan_Name(MPPA.Activity_Type,MPPA.plan_id,null) Plan_Name, ';
2559 from_query := from_query || ' msc_scn_utils.plan_status(MPPA.plan_id) Plan_Status, to_CHAR(NULL) Priority_Text, ';
2560 from_query := from_query || ' to_date(NULL) Completed_On,msc_pers_queries.get_user(MPPA.alternate_owner) Alternate_Owner_Name,msc_pers_queries.get_user(MPPA.created_by) ';
2561 from_query := from_query || ' Created_By_User, MPPA.Creation_date Creation_Date, mppa.rowid ROW_ID, MPPA.Activity_Id Activity_Id, to_number(NULL) Scenario_Id, to_number(NULL) Scenario_Set_Id, ';
2562 from_query := from_query || ' decode (to_char(decode(MPPA.activity_type,14,''Manual'',15,''Manual'',16,''Manual'',17,''Manual'',18,''Manual'',20,''Manual'',22,''Manual'',23,''Manual'',''System'' ) ),''Manual'',''MSA'',''MPPA'') ';
2563 from_query := from_query || ' SOURCE_TABLE, MPPA.Owner Owner,';
2564 from_query := from_query || ' MPPA.Status Status, MPPA.created_by, MPPA.last_update_date, MPPA.Last_updated_by, MPPA.last_update_login, to_number(NULL) Priority, MPPA.Alternate_Owner Alternate_Owner, to_number(NULL) Scenario_Owner ';
2565 from_query := from_query || ' from msc_planning_proc_activities MPPA, msc_planning_process mpp where MPPA.status in (1,2,4) and mppa.run_sequence=mpp.curr_run_sequence and mppa.process_id=mpp.process_id ) )) from_query';
2566 l_where_clause := ' where ';
2567 --dbms_output.put_line(' 1');
2568 --dbms_output.put_line(' printing from within proc: ' || where_clause);
2569 --dbms_output.put_line(' 2');
2570 l_where_clause := l_where_clause || where_clause;
2571
2572 --l_where_clause := l_where_clause || ' 1=1 AND ( (''MSA'' = SOURCE_TABLE) AND ( scenario_id is null or (1063,scenario_id) in (select user_id,scenario_id from msc_scenario_users) ) ) OR ''MPPA'' = source_table';
2573 -- create a summary query on top of form_query
2574 group_by := 'GROUP BY source_table, status, SIGN(finish_by -TRUNC(sysdate) + 0) ORDER BY 1, 3, 2';
2575 --dbms_output.put_line(' 3');
2576 sql_stmt := select_query || ' ' || from_query || ' ' || l_where_clause || ' ' || group_by;
2577 --dbms_output.put_line(' 4');
2578 --dbms_output.put_line (sql_stmt);
2579 OPEN act_cv FOR sql_stmt;
2580 LOOP
2581 FETCH act_cv INTO source_table, activity_status,ppf,status,summ;
2582 EXIT WHEN act_cv%NOTFOUND;
2583 -- process record
2584 If source_table = 'MSA' then --stuff values for Manual activities
2585 -- get values for 'PAST_DUE', 'CURRENT', 'FUTURE'
2586 --dbms_output.put_line(' 5');
2587 if ppf = 'PAST_DUE' then
2588 if STATUS = 1 THEN --Not Started
2589 tm_not_started := tm_not_started + summ;
2590 act1 := summ;
2591 elsif status=2 then-- In Progress
2592 tm_in_progress := tm_in_progress + summ;
2593 act5 := summ;
2594 elsif status = 4 then -- Error
2595 tm_error := tm_error + summ;
2596 act9 := summ;
2597 end if;
2598
2599 elsif ppf= 'CURRENT' then
2600 if STATUS = 1 THEN --Not Started
2601 tm_not_started := tm_not_started + summ;
2602 act2 := summ;
2603 elsif status=2 then -- In Progress
2604 tm_in_progress := tm_in_progress + summ;
2605 act6 := summ;
2606 elsif status = 4 then -- Error
2607 tm_error := tm_error + summ;
2608 act10 := summ;
2609 end if;
2610
2611 elsif ppf='FUTURE' then
2612 if STATUS = 1 THEN --Not Started
2613 tm_not_started := tm_not_started + summ;
2614 act3 := summ;
2615 elsif status=2 then -- In Progress
2616 tm_in_progress := tm_in_progress + summ;
2617 act7 := summ;
2618 elsif status = 4 then -- Error
2619 tm_error := tm_error + summ;
2620 act11 := summ;
2621 end if;
2622 end if;
2623 elsif source_table = 'MPPA' then --stuff values for System activities
2624
2625 -- get values for 'PAST_DUE', 'CURRENT', 'FUTURE'
2626 if ppf = 'PAST_DUE' then
2627 if STATUS = 1 THEN --Not Started
2628 sm_not_started := sm_not_started + summ;
2629 act17 := summ;
2630 elsif status=2 then -- In Progress
2631 sm_in_progress := sm_in_progress + summ;
2632 act21 := summ;
2633 elsif status = 4 then -- Error
2634 sm_error := sm_error + summ;
2635 act25 := summ;
2636 end if;
2637
2638 elsif ppf= 'CURRENT' then
2639 if STATUS = 1 THEN --Not Started
2640 sm_not_started := sm_not_started + summ;
2641 act18 := summ;
2642 elsif status=2 then -- In Progress
2643 sm_in_progress := sm_in_progress + summ;
2644 act22 := summ;
2645 elsif status = 4 then -- Error
2646 sm_error := sm_error + summ;
2647 act26 := summ;
2648 end if;
2649
2650 elsif ppf='FUTURE' then
2651 if STATUS = 1 THEN --Not Started
2652 sm_not_started := sm_not_started + summ;
2653 act19 := summ;
2654 elsif status=2 then -- In Progress
2655 sm_in_progress := sm_in_progress + summ;
2656 act23 := summ;
2657 elsif status = 4 then -- Error
2658 sm_error := sm_error + summ;
2659 act27 := summ;
2660 end if;
2661 end if;
2662 end if;
2663 END LOOP;
2664
2665 act13 := act1+act5+act9;
2666 act14 := act2+act6+act10;
2667 act15 := act3+act7+act11;
2668
2669 act29 := act17+act21+act25;
2670 act30 := act18+act22+act26;
2671 act31 := act19+act23+act27;
2672
2673 act4:= tm_not_started;
2674 act8:= tm_in_progress;
2675 act12:= tm_error;
2676 act16:= tm_not_started+tm_in_progress+tm_error;
2677
2678 act20:= sm_not_started;
2679 act24:= sm_in_progress;
2680 act28:= sm_error;
2681 act32:= sm_not_started+sm_in_progress+sm_error;
2682
2683 act33 :=tm_not_started+sm_not_started;
2684 act34 :=tm_in_progress+sm_in_progress;
2685 act35 :=tm_error+sm_error;
2686 act36 :=act33+act34+act35;
2687 CLOSE act_cv;
2688
2689 act_summary := act1||','||act2||','||act3||','||act4||','||act5||','||act6||','||act7||','||act8||','||act9||','||act10
2690 ||','||act11||','||act12||','||act13||','||act14||','||act15||','||act16||','||act17||','||act18
2691 ||','||act19||','||act20||','||act21||','||act22||','||act23||','||act24||','||act25||','||act26
2692 ||','||act27||','||act28||','||act29||','||act30||','||act31||','||act32||','||act33||','||act34
2693 ||','||act35||','||act36;
2694 activity_summary:=act_summary;
2695
2696 EXCEPTION when others THEN
2697 activity_summary:=act_summary;
2698 raise_application_error(-20000,'Failed to do the due to the following error: ' || sqlcode || sqlerrm);
2699
2700 end get_Activity_Summary;
2701
2702 function get_plan_run_date(p_plan_type in number,p_plan_id in number) return date is
2703 cursor c_run_date is
2704 select plan_completion_date
2705 from msc_plans
2706 where plan_type = p_plan_type and
2707 plan_id = p_plan_id;
2708
2709 cursor c_dem_run_date is
2710 select last_update_date
2711 from msd_dp_scenario_revisions mdr,
2712 msd_dp_ascp_scenarios_v mdas
2713 where mdr.scenario_id=mdas.scenario_id and
2714 mdr.revision = mdas.last_revision;
2715
2716 l_run_date date;
2717 begin
2718 if p_plan_type <>10 then
2719 open c_run_date;
2720 fetch c_run_date into l_run_date;
2721 close c_run_date;
2722 else
2723 open c_dem_run_date;
2724 fetch c_dem_run_date into l_run_date;
2725 close c_dem_run_date;
2726 end if;
2727
2728 return l_run_date;
2729 end get_plan_run_date;
2730
2731 function get_process_status(p_process_id in number,p_curr_run_seq in number) return number is
2732 cursor c_activity_status is
2733 select
2734 nvl(
2735 decode(s5,0,5,1,5,
2736 decode(s4,0,4,1,4,
2737 decode(s6,0,6,1,6,
2738 decode(s2,0,2,1,2,
2739 decode(s1,0,1,1,1,3)
2740 )
2741 )
2742 )
2743 ),3)
2744 proc_status from
2745 (select
2746 sign(sum(decode(status,1,1,0))-1) s1,
2747 sign(sum(decode(status,2,1,0))-1) s2,
2748 sign(sum(decode(status,3,1,0))-1) s3,
2749 sign(sum(decode(status,4,1,0))-1) s4,
2750 sign(sum(decode(status,5,1,0))-1) s5,
2751 sign(sum(decode(status,6,1,0))-1) s6
2752 from msc_planning_proc_activities
2753 where
2754 process_id = p_process_id
2755 and run_sequence = p_curr_run_seq
2756 and skip=2) a;
2757 l_process_status number := 3;
2758 begin
2759 if(p_curr_run_seq = 0) then
2760 return 1;
2761 end if;
2762 open c_activity_status;
2763 if c_activity_status%NOTFOUND then
2764 return 3;
2765 end if;
2766 fetch c_activity_status into l_process_status;
2767 close c_activity_status;
2768 return l_process_status;
2769 exception when others then
2770 return 3;
2771 end get_process_status;
2772
2773 procedure populate_default_params(p_activity_type IN OUT NOCOPY number,param_default IN OUT NOCOPY varchar2) is
2774
2775 cursor c_activity_params is
2776 SELECT sequence,sql,default_value from msc_activity_parameters
2777 where activity_type = p_activity_type and sql is not null and
2778 default_value is not null;
2779
2780 l_param_sequence NUMBER;
2781 l_sql varchar2(3000);
2782 l_default varchar2(300);
2783 l_sql_stmt varchar2(3000);
2784 l_hidden varchar2(200);
2785 l_display varchar2(200);
2786 l_return varchar2(3000) := '';
2787 begin
2788 open c_activity_params;
2789 loop
2790 fetch c_activity_params into l_param_sequence,l_sql,l_default;
2791 exit when c_activity_params%NOTFOUND;
2792 l_sql_stmt := 'select hidden,display from (' || l_sql || ') where hidden = ''' || l_default || '''';
2793
2794 EXECUTE IMMEDIATE l_sql_stmt INTO l_hidden,l_display;
2795 l_return := l_return || l_param_sequence || '#' || l_display || '#';
2796 end loop;
2797 close c_activity_params;
2798 param_default := l_return;
2799 end populate_default_params;
2800
2801 procedure create_scenario( errbuf out nocopy varchar2, retcode out nocopy
2802 varchar2,
2803 p_scn_name varchar2, p_description varchar2,
2804 p_owner number, p_scn_version date,
2805 p_scn_access number, p_scn_comment varchar2,
2806 p_valid_from date, p_valid_to date,
2807 p_plan_id_arr msc_scn_utils.number_arr,
2808 p_users_arr msc_scn_utils.number_arr
2809 ) is
2810 l_scn_id number;
2811 l_scn_name varchar2(100);
2812 l_description varchar2(240);
2813 l_owner number;
2814 l_scn_version date;
2815 l_scn_access number;
2816 l_scn_comment varchar2(4000);
2817 l_valid_from date;
2818 l_valid_to date;
2819 l_login_id number;
2820 l_user_id number;
2821
2822 l_plan_run_id number;
2823 l_plan_type number;
2824 l_status number;
2825 l_run_date date;
2826 l_plan_horizon date;
2827
2828 cursor c_scn is
2829 select scenario_id
2830 from msc_scenarios
2831 where scenario_name = p_scn_name;
2832 l_temp number;
2833
2834 cursor c_plan_type (ll_plan_id number) is
2835 select curr_plan_type
2836 from msc_plans
2837 where plan_id = ll_plan_id;
2838
2839 cursor c_scn_plan (p_scn_id number, p_plan_id number) is
2840 select count(*)
2841 from msc_scenario_plans
2842 where scenario_id = p_scn_id
2843 and plan_id = p_plan_id;
2844
2845 begin
2846 --validation begins
2847 if p_scn_name is null then
2848 errbuf := 'Scenario Name should not be null';
2849 retcode := -1;
2850 return;
2851 else
2852 open c_scn;
2853 fetch c_scn into l_scn_id;
2854 close c_scn;
2855 end if;
2856 --validation ends
2857
2858
2859 if (l_scn_id is null) then
2860 --copy all params
2861 l_scn_name := p_scn_name;
2862 l_description := p_description;
2863 l_owner := 0;
2864 l_scn_version := p_scn_version;
2865 l_scn_access := p_scn_access;
2866 l_scn_comment := p_scn_comment;
2867 l_valid_from := nvl(p_valid_from, sysdate);
2868 l_valid_to := p_valid_to;
2869 l_login_id := 0;
2870 l_user_id := 0;
2871
2872 select msc_scn_scenarios_s.nextval into l_scn_id from dual;
2873
2874 --Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2875
2876 insert into msc_scenarios (scenario_id, scenario_name,
2877 created_by, creation_date, last_update_date, last_updated_by, last_update_login,
2878 parent_scn_id, description, owner,
2879 scn_access, scn_comment, valid_from, valid_to, scn_version)
2880 values
2881 (l_scn_id, l_scn_name,
2882 l_user_id, sysdate, sysdate, l_user_id, l_login_id,
2883 to_number(null), l_description, l_owner,
2884 l_scn_access, l_scn_comment, l_valid_from, l_valid_to, to_date(null));
2885 end if;
2886
2887 for i in 1..p_plan_id_arr.count
2888 loop
2889 if (p_plan_id_arr(i) <> 0) then
2890 l_plan_run_id := null;
2891 l_plan_type := null;
2892 l_status := null;
2893 l_run_date := null;
2894 l_plan_horizon := null;
2895
2896 open c_scn_plan (l_scn_id, p_plan_id_arr(i));
2897 fetch c_scn_plan into l_temp;
2898 close c_scn_plan;
2899
2900 if (l_temp = 0) then
2901 open c_plan_type(p_plan_id_arr(i));
2902 fetch c_plan_type into l_plan_type;
2903 close c_plan_type;
2904
2905 insert into msc_scenario_plans (scenario_id, plan_type, plan_id,
2906 created_by, creation_Date, last_update_date, last_updated_by, last_update_login,
2907 status, run_date, plan_horizon, plan_run_id)
2908 values
2909 (l_scn_id, l_plan_type, p_plan_id_arr(i),
2910 l_user_id, sysdate, sysdate, l_user_id, l_login_id,
2911 l_status, l_run_date, l_plan_horizon, l_plan_run_id);
2912 end if;
2913 end if;
2914 end loop;
2915
2916 for i in 1..p_users_arr.count
2917 loop
2918 begin
2919 insert into msc_scenario_users (scenario_id, user_id,
2920 created_by, creation_Date, last_update_date, last_updated_by, last_update_login)
2921 values
2922 (l_scn_id, p_users_arr(i),
2923 l_user_id, sysdate, sysdate, l_user_id, l_login_id);
2924 exception
2925 when others then
2926 null;
2927 end;
2928 end loop;
2929
2930 commit;
2931 errbuf := null;
2932 retcode := l_scn_id;
2933 return;
2934 end create_scenario;
2935
2936
2937 END MSC_SCN_UTILS;