[Home] [Help]
PACKAGE BODY: APPS.MSC_SCN_UTILS
Source
1 PACKAGE BODY MSC_SCN_UTILS AS
2 /* $Header: MSCSCNUB.pls 120.51.12010000.5 2008/07/22 06:15:25 vkkandul 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,NULL,
1191 1,SYSDATE,'Y','Y','Shipment and Booking History -Collection Method',p_version);
1192
1193 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1194 NAME,
1195 DATA_TYPE,
1196 DEFAULT_VALUE,
1197 SEQUENCE,
1198 SQL,
1199 CREATED_BY,
1200 CREATION_DATE,
1201 REQUIRED,
1202 DISPLAYED,
1203 DISPLAY_NAME,
1204 PARAM_VERSION)
1205 VALUES(10,'DATE_RANGE_TYPE','NUMBER',NULL,4,NULL,
1206 1,SYSDATE,'Y','Y','Data Range Type',p_version);
1207
1208 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1209 NAME,
1210 DATA_TYPE,
1211 DEFAULT_VALUE,
1212 SEQUENCE,
1213 SQL,
1214 CREATED_BY,
1215 CREATION_DATE,
1216 REQUIRED,
1217 DISPLAYED,
1218 DISPLAY_NAME,
1219 PARAM_VERSION)
1220 VALUES(10,'HISTORY_COLLECTION_WINDOW','NUMBER',NULL,5,NULL,
1221 1,SYSDATE,'N','Y','Shipment and Booking History - History Collection Window',p_version);
1222
1223 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1224 NAME,
1225 DATA_TYPE,
1226 DEFAULT_VALUE,
1227 SEQUENCE,
1228 SQL,
1229 CREATED_BY,
1230 CREATION_DATE,
1231 REQUIRED,
1232 DISPLAYED,
1233 DISPLAY_NAME,
1234 PARAM_VERSION)
1235 VALUES(10,'DATE_FROM','DATE',NULL,6,NULL,
1236 1,SYSDATE,'N','Y','Shipment and Booking History - Date From',p_version);
1237
1238 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1239 NAME,
1240 DATA_TYPE,
1241 DEFAULT_VALUE,
1242 SEQUENCE,
1243 SQL,
1244 CREATED_BY,
1245 CREATION_DATE,
1246 REQUIRED,
1247 DISPLAYED,
1248 DISPLAY_NAME,
1249 PARAM_VERSION)
1250 VALUES(10,'DATE_TO','DATE',NULL,7,NULL,
1251 1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
1252
1253 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1254 NAME,
1255 DATA_TYPE,
1256 DEFAULT_VALUE,
1257 SEQUENCE,
1258 SQL,
1259 CREATED_BY,
1260 CREATION_DATE,
1261 REQUIRED,
1262 DISPLAYED,
1263 DISPLAY_NAME,
1264 PARAM_VERSION)
1265 VALUES(10,'BH_BOOKED_ITEMS_BOOKED_DATE','VARCHAR','N',8,
1266 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1267 1,SYSDATE,'Y','Y','Booking History - Booked Items - Booked Date',p_version);
1268
1269 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1270 NAME,
1271 DATA_TYPE,
1272 DEFAULT_VALUE,
1273 SEQUENCE,
1274 SQL,
1275 CREATED_BY,
1276 CREATION_DATE,
1277 REQUIRED,
1278 DISPLAYED,
1279 DISPLAY_NAME,
1280 PARAM_VERSION)
1281 VALUES(10,'BH_BOOKED_ITEMS_REQUESTED_DATE','VARCHAR','N',9,
1282 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1283 1,SYSDATE,'Y','Y','Booking History - Booked Items - Requested Date',p_version);
1284
1285 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1286 NAME,
1287 DATA_TYPE,
1288 DEFAULT_VALUE,
1289 SEQUENCE,
1290 SQL,
1291 CREATED_BY,
1292 CREATION_DATE,
1293 REQUIRED,
1294 DISPLAYED,
1295 DISPLAY_NAME,
1296 PARAM_VERSION)
1297 VALUES(10,'BH_REQUESTED_ITEMS_BOOKED_DATE','VARCHAR','N',10,
1298 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1299 1,SYSDATE,'Y','Y','Booking History - Requested Items - Booked Date',p_version);
1300
1301 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1302 NAME,
1303 DATA_TYPE,
1304 DEFAULT_VALUE,
1305 SEQUENCE,
1306 SQL,
1307 CREATED_BY,
1308 CREATION_DATE,
1309 REQUIRED,
1310 DISPLAYED,
1311 DISPLAY_NAME,
1312 PARAM_VERSION)
1313 VALUES(10,'BH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',11,
1314 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1315 1,SYSDATE,'Y','Y','Booking History - Requested Items - Requested Date',p_version);
1316
1317 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1318 NAME,
1319 DATA_TYPE,
1320 DEFAULT_VALUE,
1321 SEQUENCE,
1322 SQL,
1323 CREATED_BY,
1324 CREATION_DATE,
1325 REQUIRED,
1326 DISPLAYED,
1327 DISPLAY_NAME,
1328 PARAM_VERSION)
1329 VALUES(10,'SH_SHIPPED_ITEMS_SHIPPED_DATE','VARCHAR','N',12,
1330 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1331 1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Shipped Date',p_version);
1332
1333 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1334 NAME,
1335 DATA_TYPE,
1336 DEFAULT_VALUE,
1337 SEQUENCE,
1338 SQL,
1339 CREATED_BY,
1340 CREATION_DATE,
1341 REQUIRED,
1342 DISPLAYED,
1343 DISPLAY_NAME,
1344 PARAM_VERSION)
1345 VALUES(10,'SH_SHIPPED_ITEMS_REQUESTED_DATE','VARCHAR','N',13,
1346 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1347 1,SYSDATE,'Y','Y','ShipmentHistory - Shipped Items - Requested Date',p_version);
1348
1349 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1350 NAME,
1351 DATA_TYPE,
1352 DEFAULT_VALUE,
1353 SEQUENCE,
1354 SQL,
1355 CREATED_BY,
1356 CREATION_DATE,
1357 REQUIRED,
1358 DISPLAYED,
1359 DISPLAY_NAME,
1360 PARAM_VERSION)
1361 VALUES(10,'SH_REQUESTED_ITEMS_SHIPPED_DATE','VARCHAR','N',14,
1362 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1363 1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Shipped Date',p_version);
1364
1365 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1366 NAME,
1367 DATA_TYPE,
1368 DEFAULT_VALUE,
1369 SEQUENCE,
1370 SQL,
1371 CREATED_BY,
1372 CREATION_DATE,
1373 REQUIRED,
1374 DISPLAYED,
1375 DISPLAY_NAME,
1376 PARAM_VERSION)
1377 VALUES(10,'SH_REQUESTED_ITEMS_REQUESTED_DATE','VARCHAR','N',15,
1378 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1379 1,SYSDATE,'Y','Y','ShipmentHistory - Requested Items - Requested Date',p_version);
1380
1381 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1382 NAME,
1383 DATA_TYPE,
1384 DEFAULT_VALUE,
1385 SEQUENCE,
1386 SQL,
1387 CREATED_BY,
1388 CREATION_DATE,
1389 REQUIRED,
1390 DISPLAYED,
1391 DISPLAY_NAME,
1392 PARAM_VERSION)
1393 VALUES(10,'COLLECT_ISO','VARCHAR','N',16,
1394 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1395 1,SYSDATE,'Y','Y','Collect Internal Sales Orders',p_version);
1396
1397 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1398 NAME,
1399 DATA_TYPE,
1400 DEFAULT_VALUE,
1401 SEQUENCE,
1402 SQL,
1403 CREATED_BY,
1404 CREATION_DATE,
1405 REQUIRED,
1406 DISPLAYED,
1407 DISPLAY_NAME,
1408 PARAM_VERSION)
1409 VALUES(10,'COLLECT_ALL_ORDER_TYPES','VARCHAR','Y',17,
1410 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1411 1,SYSDATE,'Y','Y','Collect All Order Types',p_version);
1412
1413 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1414 NAME,
1415 DATA_TYPE,
1416 DEFAULT_VALUE,
1417 SEQUENCE,
1418 SQL,
1419 CREATED_BY,
1420 CREATION_DATE,
1421 REQUIRED,
1422 DISPLAYED,
1423 DISPLAY_NAME,
1424 PARAM_VERSION)
1425 VALUES(10,'INCLUDE_ORDER_TYPES','VARCHAR','N',18,NULL,1,SYSDATE,'N','Y','Include Order types',p_version);
1426
1427 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1428 NAME,
1429 DATA_TYPE,
1430 DEFAULT_VALUE,
1431 SEQUENCE,
1432 SQL,
1433 CREATED_BY,
1434 CREATION_DATE,
1435 REQUIRED,
1436 DISPLAYED,
1437 DISPLAY_NAME,
1438 PARAM_VERSION)
1439 VALUES(10,'EXCLUDE_ORDER_TYPES','VARCHAR','N',19,NULL,1,SYSDATE,'N','Y','Exclude Order types',p_version);
1440
1441
1442 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1443 NAME,
1444 DATA_TYPE,
1445 DEFAULT_VALUE,
1446 SEQUENCE,
1447 SQL,
1448 CREATED_BY,
1449 CREATION_DATE,
1450 REQUIRED,
1451 DISPLAYED,
1452 DISPLAY_NAME,
1453 PARAM_VERSION)
1454 VALUES(10,'LAUNCH_DOWNLOAD','VARCHAR','N',20,
1455 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1456 1,SYSDATE,'Y','Y','Launch Download',p_version);
1457
1458
1459 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1460 NAME,
1461 DATA_TYPE,
1462 DEFAULT_VALUE,
1463 SEQUENCE,
1464 SQL,
1465 CREATED_BY,
1466 CREATION_DATE,
1467 REQUIRED,
1468 DISPLAYED,
1469 DISPLAY_NAME,
1470 PARAM_VERSION)
1471 VALUES(10,'DATE_FROM3','DATE',NULL,21,NULL,
1472 1,SYSDATE,'N','Y','Currency Conversions - Date From',p_version);
1473
1474 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1475 NAME,
1476 DATA_TYPE,
1477 DEFAULT_VALUE,
1478 SEQUENCE,
1479 SQL,
1480 CREATED_BY,
1481 CREATION_DATE,
1482 REQUIRED,
1483 DISPLAYED,
1484 DISPLAY_NAME,
1485 PARAM_VERSION)
1486 VALUES(10,'DATE_TO3','DATE',NULL,22,NULL,
1487 1,SYSDATE,'N','Y','Currency Conversions - Date To',p_version);
1488
1489 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1490 NAME,
1491 DATA_TYPE,
1492 DEFAULT_VALUE,
1493 SEQUENCE,
1494 SQL,
1495 CREATED_BY,
1496 CREATION_DATE,
1497 REQUIRED,
1498 DISPLAYED,
1499 DISPLAY_NAME,
1500 PARAM_VERSION)
1501 VALUES(10,'COLLECT_ALL_CURRENCIES','VARCHAR','N',23,
1502 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1503 1,SYSDATE,'Y','Y','Collect All Currency Conversions',p_version);
1504
1505 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1506 NAME,
1507 DATA_TYPE,
1508 DEFAULT_VALUE,
1509 SEQUENCE,
1510 SQL,
1511 CREATED_BY,
1512 CREATION_DATE,
1513 REQUIRED,
1514 DISPLAYED,
1515 DISPLAY_NAME,
1516 PARAM_VERSION)
1517 VALUES(10,'INCLUDE_CURRENCY_LIST','VARCHAR',NULL,24,NULL,
1518 1,SYSDATE,'N','Y','Include Currency List',p_version);
1519
1520 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1521 NAME,
1522 DATA_TYPE,
1523 DEFAULT_VALUE,
1524 SEQUENCE,
1525 SQL,
1526 CREATED_BY,
1527 CREATION_DATE,
1528 REQUIRED,
1529 DISPLAYED,
1530 DISPLAY_NAME,
1531 PARAM_VERSION)
1532 VALUES(10,'EXCLUDE_CURRENCY_LIST','VARCHAR',NULL,25,NULL,
1533 1,SYSDATE,'N','Y','Exclude Currency List',p_version);
1534
1535 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1536 NAME,
1537 DATA_TYPE,
1538 DEFAULT_VALUE,
1539 SEQUENCE,
1540 SQL,
1541 CREATED_BY,
1542 CREATION_DATE,
1543 REQUIRED,
1544 DISPLAYED,
1545 DISPLAY_NAME,
1546 PARAM_VERSION)
1547 VALUES(10,'INCLUDE_ALL','VARCHAR','N',26,
1548 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1549 1,SYSDATE,'Y','Y','Collect All Unit of Measures',p_version);
1550
1551 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1552 NAME,
1553 DATA_TYPE,
1554 DEFAULT_VALUE,
1555 SEQUENCE,
1556 SQL,
1557 CREATED_BY,
1558 CREATION_DATE,
1559 REQUIRED,
1560 DISPLAYED,
1561 DISPLAY_NAME,
1562 PARAM_VERSION)
1563 VALUES(10,'INCLUDE_UOM_LIST','VARCHAR',NULL,27,NULL,
1564 1,SYSDATE,'N','Y','Include Unit of Measures',p_version);
1565
1566 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1567 NAME,
1568 DATA_TYPE,
1569 DEFAULT_VALUE,
1570 SEQUENCE,
1571 SQL,
1572 CREATED_BY,
1573 CREATION_DATE,
1574 REQUIRED,
1575 DISPLAYED,
1576 DISPLAY_NAME,
1577 PARAM_VERSION)
1578 VALUES(10,'EXCLUDE_UOM_LIST','VARCHAR',NULL,28,NULL,
1579 1,SYSDATE,'N','Y','Exclude Unit of Measures',p_version);
1580
1581 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1582 NAME,
1583 DATA_TYPE,
1584 DEFAULT_VALUE,
1585 SEQUENCE,
1586 SQL,
1587 CREATED_BY,
1588 CREATION_DATE,
1589 REQUIRED,
1590 DISPLAYED,
1591 DISPLAY_NAME,
1592 PARAM_VERSION)
1593 VALUES(10,'START_DATE','DATE',NULL,29,NULL,
1594 1,SYSDATE,'Y','Y','Pricing Data - Date From',p_version);
1595
1596 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1597 NAME,
1598 DATA_TYPE,
1599 DEFAULT_VALUE,
1600 SEQUENCE,
1601 SQL,
1602 CREATED_BY,
1603 CREATION_DATE,
1604 REQUIRED,
1605 DISPLAYED,
1606 DISPLAY_NAME,
1607 PARAM_VERSION)
1608 VALUES(10,'END_DATE','DATE',NULL,30,NULL,
1609 1,SYSDATE,'Y','Y','Pricing Data - Date To',p_version);
1610
1611 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1612 NAME,
1613 DATA_TYPE,
1614 DEFAULT_VALUE,
1615 SEQUENCE,
1616 SQL,
1617 CREATED_BY,
1618 CREATION_DATE,
1619 REQUIRED,
1620 DISPLAYED,
1621 DISPLAY_NAME,
1622 PARAM_VERSION)
1623 VALUES(10,'INCLUDE_ALL1','VARCHAR','N',31,
1624 'select decode(lookup_code, 1, ''Y'',2,''N'') hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1625 1,SYSDATE,'Y','Y','Collect all Price Lists',p_version);
1626
1627
1628 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1629 NAME,
1630 DATA_TYPE,
1631 DEFAULT_VALUE,
1632 SEQUENCE,
1633 SQL,
1634 CREATED_BY,
1635 CREATION_DATE,
1636 REQUIRED,
1637 DISPLAYED,
1638 DISPLAY_NAME,
1639 PARAM_VERSION)
1640 VALUES(10,'INCLUDE_PRICE_LIST','VARCHAR',NULL,32,NULL,
1641 1,SYSDATE,'N','Y','Include Price Lists',p_version);
1642
1643 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1644 NAME,
1645 DATA_TYPE,
1646 DEFAULT_VALUE,
1647 SEQUENCE,
1648 SQL,
1649 CREATED_BY,
1650 CREATION_DATE,
1651 REQUIRED,
1652 DISPLAYED,
1653 DISPLAY_NAME,
1654 PARAM_VERSION)
1655 VALUES(10,'EXCLUDE_PRICE_LIST','VARCHAR',NULL,33,NULL,
1656 1,SYSDATE,'N','Y','Exclude Price Lists',p_version);
1657
1658 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1659 NAME,
1660 DATA_TYPE,
1661 DEFAULT_VALUE,
1662 SEQUENCE,
1663 SQL,
1664 CREATED_BY,
1665 CREATION_DATE,
1666 REQUIRED,
1667 DISPLAYED,
1668 DISPLAY_NAME,
1669 PARAM_VERSION)
1670 VALUES(10,'COLLECTION_GROUP1','VARCHAR','-999',34,
1671 'select CODE hidden, ORG_GROUP Display FROM MSD_DEM_ORG_GROUPS_V ',
1672 1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Group',p_version);
1673
1674 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1675 NAME,
1676 DATA_TYPE,
1677 DEFAULT_VALUE,
1678 SEQUENCE,
1679 SQL,
1680 CREATED_BY,
1681 CREATION_DATE,
1682 REQUIRED,
1683 DISPLAYED,
1684 DISPLAY_NAME,
1685 PARAM_VERSION)
1686 VALUES(10,'COLLECTION_METHOD1','NUMBER',NULL,35,NULL,
1687 1,SYSDATE,'Y','Y','Supply Chain Intelligence Data - Collection Method',p_version);
1688
1689 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1690 NAME,
1691 DATA_TYPE,
1692 DEFAULT_VALUE,
1693 SEQUENCE,
1694 SQL,
1695 CREATED_BY,
1696 CREATION_DATE,
1697 REQUIRED,
1698 DISPLAYED,
1699 DISPLAY_NAME,
1700 PARAM_VERSION)
1701 VALUES(10,'DATE_RANGE_TYPE1','NUMBER',NULL,36,NULL,
1702 1,SYSDATE,'Y','Y','Data Range Type',p_version);
1703
1704
1705 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1706 NAME,
1707 DATA_TYPE,
1708 DEFAULT_VALUE,
1709 SEQUENCE,
1710 SQL,
1711 CREATED_BY,
1712 CREATION_DATE,
1713 REQUIRED,
1714 DISPLAYED,
1715 DISPLAY_NAME,
1716 PARAM_VERSION)
1717 VALUES(10,'HISTORY_COLLECTION_WINDOW1','NUMBER',NULL,37,NULL,
1718 1,SYSDATE,'N','Y','Supply Chain Intelligence Data - History Collection Window',p_version);
1719
1720
1721
1722 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1723 NAME,
1724 DATA_TYPE,
1725 DEFAULT_VALUE,
1726 SEQUENCE,
1727 SQL,
1728 CREATED_BY,
1729 CREATION_DATE,
1730 REQUIRED,
1731 DISPLAYED,
1732 DISPLAY_NAME,
1733 PARAM_VERSION)
1734 VALUES(10,'DATE_FROM1','DATE',NULL,38,NULL,
1735 1,SYSDATE,'N','Y','Supply Chain Intelligence Data - Date From',p_version);
1736
1737
1738 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1739 NAME,
1740 DATA_TYPE,
1741 DEFAULT_VALUE,
1742 SEQUENCE,
1743 SQL,
1744 CREATED_BY,
1745 CREATION_DATE,
1746 REQUIRED,
1747 DISPLAYED,
1748 DISPLAY_NAME,
1749 PARAM_VERSION)
1750 VALUES(10,'DATE_TO1','DATE',NULL,39,NULL,
1751 1,SYSDATE,'N','Y','Shipment and Booking History - Date To',p_version);
1752
1753
1754 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1755 NAME,
1756 DATA_TYPE,
1757 DEFAULT_VALUE,
1758 SEQUENCE,
1759 SQL,
1760 CREATED_BY,
1761 CREATION_DATE,
1762 REQUIRED,
1763 DISPLAYED,
1764 DISPLAY_NAME,
1765 PARAM_VERSION)
1766 VALUES(13,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1767 1,SYSDATE,'Y','Y','New Plan Name',p_version);
1768
1769 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1770 NAME,
1771 DATA_TYPE,
1772 DEFAULT_VALUE,
1773 SEQUENCE,
1774 SQL,
1775 CREATED_BY,
1776 CREATION_DATE,
1777 REQUIRED,
1778 DISPLAYED,
1779 DISPLAY_NAME,
1780 PARAM_VERSION)
1781 VALUES(13,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
1782 'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
1783 'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
1784 'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
1785 'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1786 1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
1787
1788 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1789 NAME,
1790 DATA_TYPE,
1791 DEFAULT_VALUE,
1792 SEQUENCE,
1793 SQL,
1794 CREATED_BY,
1795 CREATION_DATE,
1796 REQUIRED,
1797 DISPLAYED,
1798 DISPLAY_NAME,
1799 PARAM_VERSION)
1800 VALUES(13,'ARCHIVE_FLAG','NUMBER',2,3,
1801 'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1802 1,sysdate,'Y','Y','Archive Forecast',p_version);
1803
1804 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1805 NAME,
1806 DATA_TYPE,
1807 DEFAULT_VALUE,
1808 SEQUENCE,
1809 SQL,
1810 CREATED_BY,
1811 CREATION_DATE,
1812 REQUIRED,
1813 DISPLAYED,
1814 DISPLAY_NAME,
1815 PARAM_VERSION)
1816 VALUES(19,'NEW_PLAN_NAME','VARCHAR2',NULL,1,NULL,
1817 1,SYSDATE,'Y','Y','New Plan Name',p_version);
1818
1819 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1820 NAME,
1821 DATA_TYPE,
1822 DEFAULT_VALUE,
1823 SEQUENCE,
1824 SQL,
1825 CREATED_BY,
1826 CREATION_DATE,
1827 REQUIRED,
1828 DISPLAYED,
1829 DISPLAY_NAME,
1830 PARAM_VERSION)
1831 VALUES(19,'DATA_PROFILE_NAME','VARCHAR2',NULL,2,
1832 'SELECT SUBSTR(tq.query_name, 1, 50) hidden, SUBSTR(tq.query_name, 1, 50) display '||
1833 'FROM msd_dem_transfer_list tl, msd_dem_transfer_query tq ' ||
1834 'WHERE tl.id = tq.transfer_id AND tq.integration_type <> 1 AND tq.export_type = 1 '||
1835 'AND tq.presentation_type = 1 AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ',
1836 1,SYSDATE,'Y','Y','Data Profile name of the Forecast',p_version);
1837
1838 INSERT INTO msc_activity_parameters(ACTIVITY_TYPE,
1839 NAME,
1840 DATA_TYPE,
1841 DEFAULT_VALUE,
1842 SEQUENCE,
1843 SQL,
1844 CREATED_BY,
1845 CREATION_DATE,
1846 REQUIRED,
1847 DISPLAYED,
1848 DISPLAY_NAME,
1849 PARAM_VERSION)
1850 VALUES(19,'ARCHIVE_FLAG','NUMBER',2,3,
1851 'select lookup_code hidden, meaning display from mfg_lookups where lookup_type = ''SYS_YES_NO''',
1852 1,sysdate,'Y','Y','Archive Forecast',p_version);
1853
1854 commit;
1855
1856 end if;
1857
1858 exception
1859 when others then
1860 commit;
1861 end insert_seed_data;
1862
1863 FUNCTION get_plan_name(p_plan_type in number,p_plan_id in number, p_plan_run_id in number) return varchar2 is
1864
1865 cursor c_plan_name is
1866 select compile_designator as plan_name
1867 from msc_plans
1868 where plan_type = nvl(p_plan_type,plan_type) and plan_id = p_plan_id
1869 union
1870 select distinct scenario_name as plan_name
1871 from msd_dp_ascp_scenarios_v
1872 where scenario_id=p_plan_id and p_plan_type=10;
1873
1874 cursor c_plan_name_arch is
1875 select plan_run_name
1876 from msc_plan_runs
1877 where plan_id=p_plan_id and
1878 plan_run_id = p_plan_run_id;
1879
1880 cursor c_end_date is
1881 select end_date
1882 from msc_plan_runs
1883 where plan_run_id = p_plan_run_id;
1884
1885 l_plan_name varchar2(80);
1886 l_plan_name_arch varchar2(80);
1887 l_end_date date;
1888 begin
1889 if p_plan_run_id is null then
1890 open c_plan_name;
1891 fetch c_plan_name into l_plan_name;
1892 close c_plan_name;
1893 else
1894 open c_plan_name_arch;
1895 fetch c_plan_name_arch into l_plan_name_arch;
1896 close c_plan_name_arch;
1897 end if;
1898
1899 open c_end_date;
1900 fetch c_end_date into l_end_date;
1901 close c_end_date;
1902
1903 -- l_plan_name := l_plan_name||to_char(l_end_date);
1904
1905 if p_plan_run_id is not null and l_plan_name_arch is not null then
1906 return l_plan_name_arch;
1907 else
1908 return l_plan_name;
1909 end if;
1910 end;
1911
1912 FUNCTION get_owner_name(p_owner_id in number) return varchar2 is
1913 cursor c_owner_name is
1914 select user_name
1915 from fnd_user
1916 where user_id = p_owner_id;
1917
1918 l_user_name varchar2(80);
1919 begin
1920 open c_owner_name;
1921 fetch c_owner_name into l_user_name;
1922 close c_owner_name;
1923
1924 return l_user_name;
1925 end;
1926
1927 FUNCTION get_scn_version(p_version date) return varchar2 is
1928 l_scn_version varchar2(30);
1929 begin
1930 if p_version is null then
1931 l_scn_version := 'Current';
1932 else
1933 l_scn_version := to_char(p_version);
1934 end if;
1935
1936 return l_scn_version;
1937 end;
1938
1939 FUNCTION get_plan_version(p_plan_run_id in number) return varchar2 is
1940 cursor c_end_date is
1941 select end_date
1942 from msc_plan_runs
1943 where plan_run_id = p_plan_run_id;
1944
1945 l_pln_version varchar2(30);
1946 l_end_date date;
1947 begin
1948 if p_plan_run_id is null or p_plan_run_id = -1 then
1949 l_pln_version := 'Current';
1950 else
1951 open c_end_date;
1952 fetch c_end_date into l_end_date;
1953 close c_end_date;
1954 l_pln_version := to_char(l_end_date);
1955 end if;
1956
1957 return l_pln_version;
1958 end;
1959
1960 FUNCTION get_scenario_name(p_scenario_id in number) return varchar2 is
1961 cursor c_scn_name is
1962 select scenario_name
1963 from msc_scenarios
1964 where scenario_id = p_scenario_id;
1965
1966 l_scn_name varchar2(100);
1967 begin
1968 open c_scn_name;
1969 fetch c_scn_name into l_scn_name;
1970 close c_scn_name;
1971
1972 return l_scn_name;
1973 end;
1974
1975 FUNCTION get_proc_act_name(p_activity_type in number) return varchar2 is
1976 cursor c_act_name is
1977 select meaning from mfg_lookups
1978 where lookup_type = 'MSC_PROCESS_ACTIVITY_TYPES'
1979 and lookup_code = p_activity_type;
1980
1981 l_act_name varchar2(100);
1982 begin
1983 open c_act_name;
1984 fetch c_act_name into l_act_name;
1985 close c_act_name;
1986
1987 return l_act_name;
1988 end;
1989
1990 procedure copy_scn_plans(p_src_scnId in number, p_dest_scnId in number) is
1991 l_user_id number;
1992 l_login_id number;
1993 begin
1994 l_user_id := fnd_profile.value ('USER_ID');
1995 l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
1996 if(p_src_scnId is not null and p_dest_scnId is not null) then
1997
1998 insert into msc_scenario_plans (scenario_id,
1999 plan_type,
2000 plan_id,
2001 created_by,
2002 creation_Date ,
2003 last_update_date,
2004 last_updated_by,
2005 last_update_login,
2006 status,
2007 run_date,
2008 plan_horizon,
2009 plan_run_id)
2010 (select p_dest_scnId,
2011 plan_type,
2012 plan_id,
2013 l_user_id,
2014 sysdate,
2015 sysdate,
2016 l_user_id,
2017 l_login_id,
2018 status,
2019 run_date,
2020 plan_horizon,
2021 plan_run_id from msc_scenario_plans where scenario_id = p_src_scnId);
2022 end if;
2023 end copy_scn_plans;
2024
2025 FUNCTION get_scn_users (p_scn_id in number) return varchar2 is
2026 cursor c_scn_users is
2027 select msc_scn_utils.get_owner_name(user_id)
2028 from msc_scenario_users
2029 where scenario_id = p_scn_id;
2030
2031 l_scn_users varchar2(2000) := '';
2032 l_scn_user varchar2(100);
2033
2034 begin
2035 open c_scn_users;
2036 loop
2037 fetch c_scn_users into l_scn_user;
2038 EXIT WHEN c_scn_users%NOTFOUND;
2039 l_scn_users := l_scn_users||l_scn_user||',';
2040 end loop;
2041 close c_scn_users;
2042 l_scn_users := l_scn_users||l_scn_user;
2043
2044 return l_scn_users;
2045 end;
2046
2047 /* count of scenarios that has this plan*/
2048 function plan_scns_count(p_plan_id in number, p_scn_id in number, p_plan_run_id in number) return number is
2049 l_count number := 0;
2050
2051 cursor c_plans is
2052 select count(*) from msc_scenario_plans
2053 where scenario_id <> p_scn_id and plan_id = p_plan_id and plan_run_id = p_plan_run_id;
2054
2055 begin
2056 open c_plans;
2057 fetch c_plans into l_count;
2058 close c_plans;
2059
2060 return l_count;
2061 end;
2062
2063 /* This procedure is called to archive a scenario.
2064 Logic
2065 Archive_Scn_Conc looks at all the plans to be archived (Archive_flag in msc_scenario_plans) and
2066 for every plan_id,
2067 generate plan_run_id and update plan_run_id field for it.
2068 call populate_Details api
2069 end for;
2070 insert new record into msc_scenarios for current version of the scenario
2071 update scenario_name,version for this scenario_id by appending sysdate to it
2072 */
2073
2074
2075 procedure archive_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2076
2077 cursor c_scn_plans is
2078 select plan_type,plan_id,run_date
2079 from msc_scenario_plans
2080 where scenario_id = p_scn_id and archive_flag = 'Y';
2081
2082 l_plan_type number;
2083 l_plan_id number;
2084 l_run_date date;
2085 l_plan_name varchar2(100);
2086
2087 l_plan_run_id number;
2088 l_scn_name varchar2(50);
2089 l_err_buf varchar2(240);
2090 l_user_id number;
2091 l_login_id number;
2092 l_new_scn_id number;
2093 l_scn_count number :=0;
2094
2095 exc_error_plan_arch EXCEPTION;
2096 begin
2097 if p_scn_id is null then
2098 return;
2099 end if;
2100
2101 l_scn_name := msc_scn_utils.get_scenario_name(p_scn_id);
2102 select count(*) into l_scn_count from msc_scenarios where scenario_name like l_scn_name||' ('||sysdate||')%';
2103 l_scn_count := l_scn_count+1;
2104 msc_util.msc_debug('New Scenario Name:'||l_scn_name||' ('||sysdate||')('||l_scn_count||')');
2105 update msc_scenarios set scn_version = sysdate,scenario_name = scenario_name||' ('||sysdate||')('||l_scn_count||')' where scenario_id = p_scn_id;
2106
2107 l_user_id := fnd_profile.value ('USER_ID');
2108 l_login_id := FND_PROFILE.VALUE('LOGIN_ID');
2109
2110 select msc_scn_scenarios_s.nextval into l_new_scn_id from dual;
2111
2112 -- Insert rows into msc_scenarios,msc_scenario_plans,msc_scenario_users;
2113
2114 insert into msc_scenarios(scenario_id,
2115 scenario_name,
2116 created_by,
2117 creation_date,
2118 last_update_date,
2119 last_updated_by,
2120 last_update_login,
2121 parent_scn_id,
2122 description,
2123 owner,
2124 scn_access,
2125 scn_comment,
2126 valid_from,
2127 valid_to,
2128 scn_version)
2129 (select l_new_scn_id,
2130 l_scn_name,
2131 l_user_id,
2132 sysdate,
2133 sysdate,
2134 l_user_id,
2135 l_login_id,
2136 parent_scn_id,
2137 description,
2138 owner,
2139 scn_access,
2140 scn_comment,
2141 valid_from,
2142 valid_to,
2143 null
2144 from msc_scenarios where scenario_id = p_scn_id);
2145
2146 insert into msc_scenario_plans (scenario_id,
2147 plan_type,
2148 plan_id,
2149 created_by,
2150 creation_Date ,
2151 last_update_date,
2152 last_updated_by,
2153 last_update_login,
2154 status,
2155 run_date,
2156 plan_horizon,
2157 plan_run_id)
2158 (select l_new_scn_id,
2159 plan_type,
2160 plan_id,
2161 l_user_id,
2162 sysdate,
2163 sysdate,
2164 l_user_id,
2165 l_login_id,
2166 status,
2167 run_date,
2168 plan_horizon,
2169 plan_run_id from msc_scenario_plans where scenario_id = p_scn_id);
2170
2171 insert into msc_scenario_users (scenario_id,
2172 user_id,
2173 created_by,
2174 creation_Date,
2175 last_update_date,
2176 last_updated_by,
2177 last_update_login)
2178 (select l_new_scn_id,
2179 user_id,
2180 l_user_id,
2181 sysdate,
2182 sysdate,
2183 l_user_id,
2184 l_login_id from msc_scenario_users where scenario_id = p_scn_id);
2185 commit;
2186
2187 open c_scn_plans;
2188 loop
2189 fetch c_scn_plans into l_plan_type,l_plan_id,l_run_date;
2190 exit when c_scn_plans%NOTFOUND;
2191 l_plan_name := msc_scn_utils.get_plan_name(l_plan_type,l_plan_id,null);
2192 if(l_plan_type <> 10) then
2193
2194 l_plan_run_id := msc_phub_pkg.populate_plan_run_info(l_plan_id,l_plan_name,l_plan_type,l_run_date);
2195 if(l_plan_type = 6) then -- for sno plans
2196 msc_phub_pkg.populate_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2197 else
2198 msc_phub_pkg.populate_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2199 end if;
2200 else -- Demantra scenarios archival
2201 l_plan_run_id := msc_phub_pkg.populate_plan_run_info(l_plan_id,null,l_plan_type,null);
2202 msc_phub_pkg.populate_demantra_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2203
2204 end if;
2205
2206 if retcode <> 0 then
2207 --null; -- errored out
2208 msc_util.msc_debug('Archive Plan Failed for Plan:'||get_plan_name(l_plan_type,l_plan_id,l_plan_run_id));
2209 raise exc_error_plan_arch;
2210 end if;
2211 update msc_scenario_plans set plan_run_id = l_plan_run_id where
2212 scenario_id = p_scn_id and plan_id = l_plan_id and plan_type = l_plan_type;
2213
2214 commit;
2215
2216 end loop;
2217 close c_scn_plans;
2218
2219 EXCEPTION
2220 when exc_error_plan_arch then
2221 if c_scn_plans%isopen then
2222 Close c_scn_plans;
2223 end if;
2224
2225 retcode := 2;
2226
2227 when OTHERS THEN
2228 retcode := 2;
2229 errbuf := sqlerrm;
2230
2231 end archive_scn_conc;
2232
2233 procedure purge_scn_conc( errbuf out nocopy varchar2, retcode out nocopy varchar2 , p_scn_id in number) is
2234
2235 cursor c_scn_plans is
2236 select plan_type,plan_id,plan_run_id
2237 from msc_scenario_plans
2238 where scenario_id = p_scn_id and purge_flag = 'Y' and plan_run_id is not null;
2239
2240 cursor c_scn_version is
2241 select scn_version
2242 from msc_scenarios
2243 where scenario_id = p_scn_id;
2244
2245 l_scn_version date;
2246 l_plan_type number;
2247 l_plan_id number;
2248 l_plan_run_id number;
2249 exc_error_purge_scn_pln EXCEPTION;
2250 begin
2251 open c_scn_version;
2252 fetch c_scn_version into l_scn_version;
2253 close c_scn_version;
2254
2255 if l_scn_version is not null then
2256 open c_scn_plans;
2257
2258 loop
2259 fetch c_scn_plans into l_plan_type,l_plan_id,l_plan_run_id;
2260 exit when c_scn_plans%NOTFOUND;
2261
2262 if(l_plan_type <> 10) then
2263
2264 if(l_plan_type = 6) then -- for sno plans
2265 msc_phub_pkg.purge_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2266 -- null;
2267 else
2268 msc_phub_pkg.purge_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2269 end if;
2270 else -- Demantra plan purge
2271 msc_phub_pkg.purge_demantra_details(errbuf,retcode,l_plan_id,l_plan_run_id);
2272 end if;
2273
2274 if retcode <> 0 then
2275 msc_util.msc_debug('Purge Plan Failed for Plan:'||get_plan_name(l_plan_type,l_plan_id,l_plan_run_id));
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_id in number, p_plan_type in number) is
2306 exc_error_purge_plan EXCEPTION;
2307 begin
2308 if(p_plan_type <> 10) then
2309
2310 if(p_plan_type = 6) then -- for sno plans
2311 msc_phub_pkg.purge_details(errbuf,retcode,p_plan_id,null);
2312 -- null;
2313 else
2314 msc_phub_pkg.purge_details(errbuf,retcode,p_plan_id,null);
2315 end if;
2316 else -- Demantra plan purge
2317 msc_phub_pkg.purge_demantra_details(errbuf,retcode,p_plan_id,null);
2318 end if;
2319
2320 if retcode <> 0 then
2321 --null; -- errored out
2322 raise exc_error_purge_plan;
2323 end if;
2324
2325 delete from msc_scenario_plans where plan_id = p_plan_id and plan_type = p_plan_type;
2326 commit;
2327 EXCEPTION
2328 when exc_error_purge_plan then
2329 retcode := 2;
2330
2331 when OTHERS THEN
2332 retcode := 2;
2333 errbuf := sqlerrm;
2334
2335 end purge_plan_conc;
2336
2337
2338
2339 /*==================================
2340 query_id --> Activity_type
2341 char1 --> param_name
2342 char2 ---> hidden value
2343 char3 ---> display_value
2344
2345 ====================================*/
2346 procedure populate_act_params_for_lov(p_activity_type in number) is
2347
2348 cursor c_activity_params is
2349 select name,sql
2350 from msc_activity_parameters
2351 where activity_type = p_activity_type and sql is not null;
2352
2353 l_count number :=0;
2354 l_param_name varchar2(80);
2355 l_sql varchar2(2000);
2356 l_substr varchar2(2000);
2357 l_insert varchar2(2000);
2358 l_select varchar2(2000);
2359 l_sql_stmnt varchar2(3000);
2360 begin
2361 IF p_activity_type = -99 THEN
2362 insert_seed_data;
2363 RETURN;
2364 END IF;
2365
2366 select count(*) into l_count from msc_form_query where query_id = p_activity_type;
2367
2368 if nvl(l_count,0) >0 then
2369 return;
2370 end if;
2371
2372 open c_activity_params;
2373 loop
2374 fetch c_activity_params into l_param_name,l_sql;
2375 exit when c_activity_params%NOTFOUND;
2376
2377 l_substr := substr(l_sql,instr(upper(l_sql),'SELECT ')+7);
2378 l_insert := 'insert into msc_form_query (query_id,
2379 char1,
2380 last_update_date,
2381 last_updated_by,
2382 last_update_login,
2383 creation_date,
2384 created_by,
2385 char2,
2386 char3)';
2387 l_select := ' select '||p_activity_type||','||''''||l_param_name||''''||','||'sysdate,1,1,sysdate,1,'||l_substr;
2388
2389 l_sql_stmnt := l_insert||l_select;
2390
2391 commit;
2392 msc_get_name.execute_dsql(l_sql_stmnt);
2393
2394 commit;
2395 end loop;
2396 close c_activity_params;
2397 end populate_act_params_for_lov;
2398
2399 FUNCTION Scenario_Status(p_Scenario_id in number) return varchar2 is
2400 begin
2401 /*
2402 This function returns the status of a scenario based on both
2403 manual activities and plans in that scenario.
2404
2405 We will look into two tables i.e
2406 MSC_SCENARIO_ACTIVITIES
2407 MSC_SCENARIO_PLANS
2408 Logic for deriving Status is as follows:
2409
2410 Scenario status When this condition is satisfied
2411 --------------- --------------------------------
2412 Not started When all activities (user and system) are not started
2413 In Progress When atleast 1 activity (user or system) is in progress
2414 Completed When all activities (user and system) are complete
2415 Error When atleast 1 activity (user or system) is in error state
2416 Warning When atleast 1 activity (user or system) is in warning state
2417
2418
2419 MSC_SCN_PLAN_STATUS 1 Completed
2420 MSC_SCN_PLAN_STATUS 2 Error
2421 MSC_SCN_PLAN_STATUS 3 Warning
2422 MSC_SCN_PLAN_STATUS 4 In Progress
2423 */
2424 return to_char(null);
2425 end Scenario_Status;
2426
2427 FUNCTION plan_Status(p_Plan_id in number) return varchar2 is
2428 cursor c_plan_status(c_plan_id number) is
2429 Select
2430 msc_Get_name.lookup_meaning(
2431 'MSC_SCN_PLAN_STATUS',
2432 decode(upper(fcr.status_code),'C',4,'E',3)
2433 ) plan_status_display
2434 from msc_plans mp,
2435 msc_plan_runs mpr,
2436 fnd_concurrent_requests fcr
2437 where mp.plan_type is not null and
2438 mp.plan_id = mpr.plan_id and
2439 mpr.end_date is not null and
2440 mp.plan_completion_date is not null and
2441 mp.request_id = fcr.request_id
2442 and mp.plan_id = c_plan_id;
2443 l_status varchar2(100) :=null;
2444
2445 begin
2446 if p_plan_id is null then
2447 return to_char(null);
2448 end if;
2449 open c_plan_Status(p_plan_id);
2450 fetch c_plan_status into l_status;
2451 close c_plan_status;
2452 return to_char(l_status);
2453 end;
2454
2455
2456 FUNCTION get_scenario_set_name(p_scenario_set_id in number) return varchar2 is
2457 cursor c_scn_set_name is
2458 select scenario_set_name
2459 from msc_scenario_sets
2460 where scenario_set_id = p_scenario_set_id;
2461
2462 l_scn_set_name varchar2(100);
2463 begin
2464 open c_scn_set_name;
2465 fetch c_scn_set_name into l_scn_set_name;
2466 close c_scn_set_name;
2467
2468 return l_scn_set_name;
2469 end;
2470
2471 procedure get_Activity_Summary(where_clause IN OUT NOCOPY varchar2, activity_summary In out NOCOPY varchar2) is
2472 TYPE DynaCurTyp IS REF CURSOR;
2473 act_cv DynaCurTyp;
2474 sql_stmt VARCHAR2(32000) := null;
2475 select_query varchar2(32000) := null;
2476 from_query varchar2(32000) := null;
2477 l_where_clause varchar2(32000) := null;
2478 group_by varchar2(32000) :=null;
2479
2480 source_table varchar2(1000);
2481 activity_status varchar2(1000);
2482 ppf varchar2(1000);
2483 status number;
2484 summ number;
2485
2486 act1 number :=0;
2487 act2 number :=0;
2488 act3 number :=0;
2489 act4 number :=0;
2490 act5 number :=0;
2491 act6 number :=0;
2492 act7 number :=0;
2493 act8 number :=0;
2494 act9 number :=0;
2495 act10 number :=0;
2496 act11 number :=0;
2497 act12 number :=0;
2498 act13 number :=0;
2499 act14 number :=0;
2500 act15 number :=0;
2501 act16 number :=0;
2502 act17 number :=0;
2503 act18 number :=0;
2504 act19 number :=0;
2505 act20 number :=0;
2506 act21 number :=0;
2507 act22 number :=0;
2508 act23 number :=0;
2509 act24 number :=0;
2510 act25 number :=0;
2511 act26 number :=0;
2512 act27 number :=0;
2513 act28 number :=0;
2514 act29 number :=0;
2515 act30 number :=0;
2516 act31 number :=0;
2517 act32 number :=0;
2518 act33 number :=0;
2519 act34 number :=0;
2520 act35 number :=0;
2521 act36 number :=0;
2522 tm_not_started number :=0;
2523 sm_not_started number :=0;
2524 tm_in_progress number :=0;
2525 sm_in_progress number :=0;
2526 tm_error number :=0;
2527 sm_error number :=0;
2528 act_summary varchar2(32000):=null;
2529
2530 begin
2531 act_summary := act1||','||act2||','||act3||','||act4||','||act5||','||act6||','||act7||','||act8||','||act9||','||act10
2532 ||','||act11||','||act12||','||act13||','||act14||','||act15||','||act16||','||act17||','||act18
2533 ||','||act19||','||act20||','||act21||','||act22||','||act23||','||act24||','||act25||','||act26
2534 ||','||act27||','||act28||','||act29||','||act30||','||act31||','||act32||','||act33||','||act34
2535 ||','||act35||','||act36;
2536 -- select all columns in vo as required
2537 select_query := ' SELECT source_table, msc_get_name.lookup_meaning(''MSC_SCN_ACTIVITY_STATES'', status) activity_status, ' ;
2538 select_query:= select_query || ' decode(SIGN(finish_by -TRUNC(sysdate) + 0), -1, ''PAST_DUE'', 0, ''CURRENT'', 1, ''FUTURE'') ppf, ';
2539 select_query:= select_query || ' status, COUNT(activity_id) summ ';
2540 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, ';
2541 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, ';
2542 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 ';
2543 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, ';
2544 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, ';
2545 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, ';
2546 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, ';
2547 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, ';
2548 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, ';
2549 from_query := from_query || ' MSC_SCENARIOS MS, MSC_SCENARIO_SETS MSS WHERE ';
2550 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 ';
2551 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, ';
2552 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)) ';
2553 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, ';
2554 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, ';
2555 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, ';
2556 from_query := from_query || ' msc_scn_utils.plan_status(MPPA.plan_id) Plan_Status, to_CHAR(NULL) Priority_Text, ';
2557 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) ';
2558 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, ';
2559 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'') ';
2560 from_query := from_query || ' SOURCE_TABLE, MPPA.Owner Owner,';
2561 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 ';
2562 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';
2563 l_where_clause := ' where ';
2564 --dbms_output.put_line(' 1');
2565 --dbms_output.put_line(' printing from within proc: ' || where_clause);
2566 --dbms_output.put_line(' 2');
2567 l_where_clause := l_where_clause || where_clause;
2568
2569 --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';
2570 -- create a summary query on top of form_query
2571 group_by := 'GROUP BY source_table, status, SIGN(finish_by -TRUNC(sysdate) + 0) ORDER BY 1, 3, 2';
2572 --dbms_output.put_line(' 3');
2573 sql_stmt := select_query || ' ' || from_query || ' ' || l_where_clause || ' ' || group_by;
2574 --dbms_output.put_line(' 4');
2575 --dbms_output.put_line (sql_stmt);
2576 OPEN act_cv FOR sql_stmt;
2577 LOOP
2578 FETCH act_cv INTO source_table, activity_status,ppf,status,summ;
2579 EXIT WHEN act_cv%NOTFOUND;
2580 -- process record
2581 If source_table = 'MSA' then --stuff values for Manual activities
2582 -- get values for 'PAST_DUE', 'CURRENT', 'FUTURE'
2583 --dbms_output.put_line(' 5');
2584 if ppf = 'PAST_DUE' then
2585 if STATUS = 1 THEN --Not Started
2586 tm_not_started := tm_not_started + summ;
2587 act1 := summ;
2588 elsif status=2 then-- In Progress
2589 tm_in_progress := tm_in_progress + summ;
2590 act5 := summ;
2591 elsif status = 4 then -- Error
2592 tm_error := tm_error + summ;
2593 act9 := summ;
2594 end if;
2595
2596 elsif ppf= 'CURRENT' then
2597 if STATUS = 1 THEN --Not Started
2598 tm_not_started := tm_not_started + summ;
2599 act2 := summ;
2600 elsif status=2 then -- In Progress
2601 tm_in_progress := tm_in_progress + summ;
2602 act6 := summ;
2603 elsif status = 4 then -- Error
2604 tm_error := tm_error + summ;
2605 act10 := summ;
2606 end if;
2607
2608 elsif ppf='FUTURE' then
2609 if STATUS = 1 THEN --Not Started
2610 tm_not_started := tm_not_started + summ;
2611 act3 := summ;
2612 elsif status=2 then -- In Progress
2613 tm_in_progress := tm_in_progress + summ;
2614 act7 := summ;
2615 elsif status = 4 then -- Error
2616 tm_error := tm_error + summ;
2617 act11 := summ;
2618 end if;
2619 end if;
2620 elsif source_table = 'MPPA' then --stuff values for System activities
2621
2622 -- get values for 'PAST_DUE', 'CURRENT', 'FUTURE'
2623 if ppf = 'PAST_DUE' then
2624 if STATUS = 1 THEN --Not Started
2625 sm_not_started := sm_not_started + summ;
2626 act17 := summ;
2627 elsif status=2 then -- In Progress
2628 sm_in_progress := sm_in_progress + summ;
2629 act21 := summ;
2630 elsif status = 4 then -- Error
2631 sm_error := sm_error + summ;
2632 act25 := summ;
2633 end if;
2634
2635 elsif ppf= 'CURRENT' then
2636 if STATUS = 1 THEN --Not Started
2637 sm_not_started := sm_not_started + summ;
2638 act18 := summ;
2639 elsif status=2 then -- In Progress
2640 sm_in_progress := sm_in_progress + summ;
2641 act22 := summ;
2642 elsif status = 4 then -- Error
2643 sm_error := sm_error + summ;
2644 act26 := summ;
2645 end if;
2646
2647 elsif ppf='FUTURE' then
2648 if STATUS = 1 THEN --Not Started
2649 sm_not_started := sm_not_started + summ;
2650 act19 := summ;
2651 elsif status=2 then -- In Progress
2652 sm_in_progress := sm_in_progress + summ;
2653 act23 := summ;
2654 elsif status = 4 then -- Error
2655 sm_error := sm_error + summ;
2656 act27 := summ;
2657 end if;
2658 end if;
2659 end if;
2660 END LOOP;
2661
2662 act13 := act1+act5+act9;
2663 act14 := act2+act6+act10;
2664 act15 := act3+act7+act11;
2665
2666 act29 := act17+act21+act25;
2667 act30 := act18+act22+act26;
2668 act31 := act19+act23+act27;
2669
2670 act4:= tm_not_started;
2671 act8:= tm_in_progress;
2672 act12:= tm_error;
2673 act16:= tm_not_started+tm_in_progress+tm_error;
2674
2675 act20:= sm_not_started;
2676 act24:= sm_in_progress;
2677 act28:= sm_error;
2678 act32:= sm_not_started+sm_in_progress+sm_error;
2679
2680 act33 :=tm_not_started+sm_not_started;
2681 act34 :=tm_in_progress+sm_in_progress;
2682 act35 :=tm_error+sm_error;
2683 act36 :=act33+act34+act35;
2684 CLOSE act_cv;
2685
2686 act_summary := act1||','||act2||','||act3||','||act4||','||act5||','||act6||','||act7||','||act8||','||act9||','||act10
2687 ||','||act11||','||act12||','||act13||','||act14||','||act15||','||act16||','||act17||','||act18
2688 ||','||act19||','||act20||','||act21||','||act22||','||act23||','||act24||','||act25||','||act26
2689 ||','||act27||','||act28||','||act29||','||act30||','||act31||','||act32||','||act33||','||act34
2690 ||','||act35||','||act36;
2691 activity_summary:=act_summary;
2692
2693 EXCEPTION when others THEN
2694 activity_summary:=act_summary;
2695 raise_application_error(-20000,'Failed to do the due to the following error: ' || sqlcode || sqlerrm);
2696
2697 end get_Activity_Summary;
2698
2699 function get_plan_run_date(p_plan_type in number,p_plan_id in number) return date is
2700 cursor c_run_date is
2701 select plan_completion_date
2702 from msc_plans
2703 where plan_type = p_plan_type and
2704 plan_id = p_plan_id;
2705
2706 cursor c_dem_run_date is
2707 select last_update_date
2708 from msd_dp_scenario_revisions mdr,
2709 msd_dp_ascp_scenarios_v mdas
2710 where mdr.scenario_id=mdas.scenario_id and
2711 mdr.revision = mdas.last_revision;
2712
2713 l_run_date date;
2714 begin
2715 if p_plan_type <>10 then
2716 open c_run_date;
2717 fetch c_run_date into l_run_date;
2718 close c_run_date;
2719 else
2720 open c_dem_run_date;
2721 fetch c_dem_run_date into l_run_date;
2722 close c_dem_run_date;
2723 end if;
2724
2725 return l_run_date;
2726 end get_plan_run_date;
2727
2728 function get_process_status(p_process_id in number,p_curr_run_seq in number) return number is
2729 cursor c_activity_status is
2730 select
2731 nvl(
2732 decode(s5,0,5,1,5,
2733 decode(s4,0,4,1,4,
2734 decode(s6,0,6,1,6,
2735 decode(s2,0,2,1,2,
2736 decode(s1,0,1,1,1,3)
2737 )
2738 )
2739 )
2740 ),3)
2741 proc_status from
2742 (select
2743 sign(sum(decode(status,1,1,0))-1) s1,
2744 sign(sum(decode(status,2,1,0))-1) s2,
2745 sign(sum(decode(status,3,1,0))-1) s3,
2746 sign(sum(decode(status,4,1,0))-1) s4,
2747 sign(sum(decode(status,5,1,0))-1) s5,
2748 sign(sum(decode(status,6,1,0))-1) s6
2749 from msc_planning_proc_activities
2750 where
2751 process_id = p_process_id
2752 and run_sequence = p_curr_run_seq
2753 and skip=2) a;
2754 l_process_status number := 3;
2755 begin
2756 if(p_curr_run_seq = 0) then
2757 return 1;
2758 end if;
2759 open c_activity_status;
2760 if c_activity_status%NOTFOUND then
2761 return 3;
2762 end if;
2763 fetch c_activity_status into l_process_status;
2764 close c_activity_status;
2765 return l_process_status;
2766 exception when others then
2767 return 3;
2768 end get_process_status;
2769
2770 procedure populate_default_params(p_activity_type IN OUT NOCOPY number,param_default IN OUT NOCOPY varchar2) is
2771
2772 cursor c_activity_params is
2773 SELECT sequence,sql,default_value from msc_activity_parameters
2774 where activity_type = p_activity_type and sql is not null and
2775 default_value is not null;
2776
2777 l_param_sequence NUMBER;
2778 l_sql varchar2(3000);
2779 l_default varchar2(300);
2780 l_sql_stmt varchar2(3000);
2781 l_hidden varchar2(200);
2782 l_display varchar2(200);
2783 l_return varchar2(3000) := '';
2784 begin
2785 open c_activity_params;
2786 loop
2787 fetch c_activity_params into l_param_sequence,l_sql,l_default;
2788 exit when c_activity_params%NOTFOUND;
2789 l_sql_stmt := 'select hidden,display from (' || l_sql || ') where hidden = ''' || l_default || '''';
2790
2791 EXECUTE IMMEDIATE l_sql_stmt INTO l_hidden,l_display;
2792 l_return := l_return || l_param_sequence || '#' || l_display || '#';
2793 end loop;
2794 close c_activity_params;
2795 param_default := l_return;
2796 end populate_default_params;
2797
2798 END MSC_SCN_UTILS;