[Home] [Help]
PACKAGE BODY: APPS.MSD_SEED
Source
1 PACKAGE BODY MSD_SEED AS
2 /* $Header: msdseedb.pls 115.5 2001/06/22 11:32:39 pkm ship $ */
3
4 PROCEDURE insert_levels IS
5 begin
6
7 insert into msd_levels (
8 LEVEL_ID,
9 LEVEL_NAME,
10 DESCRIPTION,
11 DIMENSION_CODE,
12 LEVEL_TYPE_CODE,
13 LAST_UPDATE_DATE,
14 LAST_UPDATED_BY,
15 CREATION_DATE,
16 CREATED_BY
17 )
18 SELECT
19 1,
20 'Item',
21 'Item Level',
22 'PRD',
23 2,
24 sysdate,
25 1,
26 sysdate,
27 1
28 FROM dual
29 where not exists (
30 select 1
31 from msd_levels
32 where level_id = 1);
33
34
35 insert into msd_levels (
36 LEVEL_ID,
37 LEVEL_NAME,
38 DESCRIPTION,
39 DIMENSION_CODE,
40 LEVEL_TYPE_CODE,
41 LAST_UPDATE_DATE,
42 LAST_UPDATED_BY,
43 CREATION_DATE,
44 CREATED_BY
45 )
46 SELECT
47 2,
48 'Product Category',
49 'Product Category Level',
50 'PRD',
51 3,
52 sysdate,
53 1,
54 sysdate,
55 1
56 FROM dual
57 where not exists (
58 select 1
59 from msd_levels
60 where level_id = 2);
61
62
63 insert into msd_levels (
64 LEVEL_ID,
65 LEVEL_NAME,
66 DESCRIPTION,
67 DIMENSION_CODE,
68 LEVEL_TYPE_CODE,
69 LAST_UPDATE_DATE,
70 LAST_UPDATED_BY,
71 CREATION_DATE,
72 CREATED_BY
73 )
74 SELECT
75 3,
76 'Product Family',
77 'Product Family Level',
78 'PRD',
79 3,
80 sysdate,
81 1,
82 sysdate,
83 1
84 FROM dual
85 where not exists (
86 select 1
87 from msd_levels
88 where level_id = 3);
89
90 insert into msd_levels (
91 LEVEL_ID,
92 LEVEL_NAME,
93 DESCRIPTION,
94 DIMENSION_CODE,
95 LEVEL_TYPE_CODE,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 CREATION_DATE,
99 CREATED_BY
100 )
101 SELECT
102 7,
103 'Organization',
104 'Organization Level',
105 'ORG',
106 2,
107 sysdate,
108 1,
109 sysdate,
110 1
111 FROM dual
112 where not exists (
113 select 1
114 from msd_levels
115 where level_id = 7);
116
117
118 insert into msd_levels (
119 LEVEL_ID,
120 LEVEL_NAME,
121 DESCRIPTION,
122 DIMENSION_CODE,
123 LEVEL_TYPE_CODE,
124 LAST_UPDATE_DATE,
125 LAST_UPDATED_BY,
126 CREATION_DATE,
127 CREATED_BY
128 )
129 SELECT
130 8,
131 'Operating Unit',
132 'Operating Unit Level',
133 'ORG',
134 3,
135 sysdate,
136 1,
137 sysdate,
138 1
139 FROM dual
140 where not exists (
141 select 1
142 from msd_levels
143 where level_id = 8);
144
145
146 insert into msd_levels (
147 LEVEL_ID,
148 LEVEL_NAME,
149 DESCRIPTION,
150 DIMENSION_CODE,
151 LEVEL_TYPE_CODE,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY,
154 CREATION_DATE,
155 CREATED_BY
156 )
157 SELECT
158 9,
159 'Legal Entity',
160 'Legal Entity Level',
161 'ORG',
162 3,
163 sysdate,
164 1,
165 sysdate,
166 1
167 FROM dual
168 where not exists (
169 select 1
170 from msd_levels
171 where level_id = 9);
172
173
174 insert into msd_levels (
175 LEVEL_ID,
176 LEVEL_NAME,
177 DESCRIPTION,
178 DIMENSION_CODE,
179 LEVEL_TYPE_CODE,
180 LAST_UPDATE_DATE,
181 LAST_UPDATED_BY,
182 CREATION_DATE,
183 CREATED_BY
184 )
185 SELECT
186 10,
187 'Business Group',
188 'Business Group Level',
189 'ORG',
190 3,
191 sysdate,
192 1,
193 sysdate,
194 1
195 FROM dual
196 where not exists (
197 select 1
198 from msd_levels
199 where level_id = 10);
200
201
202 insert into msd_levels (
203 LEVEL_ID,
204 LEVEL_NAME,
205 DESCRIPTION,
206 DIMENSION_CODE,
207 LEVEL_TYPE_CODE,
208 LAST_UPDATE_DATE,
209 LAST_UPDATED_BY,
210 CREATION_DATE,
211 CREATED_BY
212 )
213 SELECT
214 11,
215 'Ship To Location',
216 'Ship To Location Level',
217 'GEO',
218 2,
219 sysdate,
220 1,
221 sysdate,
222 1
223 FROM dual
224 where not exists (
225 select 1
226 from msd_levels
227 where level_id = 11);
228
229
230 insert into msd_levels (
231 LEVEL_ID,
232 LEVEL_NAME,
233 DESCRIPTION,
234 DIMENSION_CODE,
235 LEVEL_TYPE_CODE,
236 LAST_UPDATE_DATE,
237 LAST_UPDATED_BY,
238 CREATION_DATE,
239 CREATED_BY
240 )
241 SELECT
242 12,
243 'Region',
244 'Region Level',
245 'GEO',
246 3,
247 sysdate,
248 1,
249 sysdate,
250 1
251 FROM dual
252 where not exists (
253 select 1
254 from msd_levels
255 where level_id = 12);
256
257
258 insert into msd_levels (
259 LEVEL_ID,
260 LEVEL_NAME,
261 DESCRIPTION,
262 DIMENSION_CODE,
263 LEVEL_TYPE_CODE,
264 LAST_UPDATE_DATE,
265 LAST_UPDATED_BY,
266 CREATION_DATE,
267 CREATED_BY
268 )
269 SELECT
270 13,
271 'Country',
272 'Country Level',
273 'GEO',
274 3,
275 sysdate,
276 1,
277 sysdate,
278 1
279 FROM dual
280 where not exists (
281 select 1
282 from msd_levels
283 where level_id = 13);
284
285
286 insert into msd_levels (
287 LEVEL_ID,
288 LEVEL_NAME,
289 DESCRIPTION,
290 DIMENSION_CODE,
291 LEVEL_TYPE_CODE,
292 LAST_UPDATE_DATE,
293 LAST_UPDATED_BY,
294 CREATION_DATE,
295 CREATED_BY
296 )
297 SELECT
298 14,
299 'Area',
300 'Area Level',
301 'GEO',
302 3,
303 sysdate,
304 1,
305 sysdate,
306 1
307 FROM dual
308 where not exists (
309 select 1
310 from msd_levels
311 where level_id = 14);
312
313
314 insert into msd_levels (
315 LEVEL_ID,
316 LEVEL_NAME,
317 DESCRIPTION,
318 DIMENSION_CODE,
319 LEVEL_TYPE_CODE,
320 LAST_UPDATE_DATE,
321 LAST_UPDATED_BY,
322 CREATION_DATE,
323 CREATED_BY
324 )
325 SELECT
326 15,
327 'Customer',
328 'Customer Level',
329 'GEO',
330 3,
331 sysdate,
332 1,
333 sysdate,
334 1
335 FROM dual
336 where not exists (
337 select 1
338 from msd_levels
339 where level_id = 15);
340
341
342 insert into msd_levels (
343 LEVEL_ID,
344 LEVEL_NAME,
345 DESCRIPTION,
346 DIMENSION_CODE,
347 LEVEL_TYPE_CODE,
348 LAST_UPDATE_DATE,
349 LAST_UPDATED_BY,
350 CREATION_DATE,
351 CREATED_BY
352 )
353 SELECT
354 16,
355 'Customer Class',
356 'Customer Class Level',
357 'GEO',
358 3,
359 sysdate,
360 1,
361 sysdate,
362 1
363 FROM dual
364 where not exists (
365 select 1
366 from msd_levels
367 where level_id = 16);
368
369
370 insert into msd_levels (
371 LEVEL_ID,
372 LEVEL_NAME,
373 DESCRIPTION,
374 DIMENSION_CODE,
375 LEVEL_TYPE_CODE,
376 LAST_UPDATE_DATE,
377 LAST_UPDATED_BY,
378 CREATION_DATE,
379 CREATED_BY
380 )
381 SELECT
382 17,
383 'Customer Group',
384 'Customer Group Level',
385 'GEO',
386 3,
387 sysdate,
388 1,
389 sysdate,
390 1
391 FROM dual
392 where not exists (
393 select 1
394 from msd_levels
395 where level_id = 17);
396
397
398 insert into msd_levels (
399 LEVEL_ID,
400 LEVEL_NAME,
401 DESCRIPTION,
402 DIMENSION_CODE,
403 LEVEL_TYPE_CODE,
404 LAST_UPDATE_DATE,
405 LAST_UPDATED_BY,
406 CREATION_DATE,
407 CREATED_BY
408 )
409 SELECT
410 18,
411 'Sales Representative',
412 'Sales Rep Level',
413 'REP',
414 2,
415 sysdate,
416 1,
417 sysdate,
418 1
419 FROM dual
420 where not exists (
421 select 1
422 from msd_levels
423 where level_id = 18);
424
425
426 insert into msd_levels (
427 LEVEL_ID,
428 LEVEL_NAME,
429 DESCRIPTION,
430 DIMENSION_CODE,
431 LEVEL_TYPE_CODE,
432 LAST_UPDATE_DATE,
433 LAST_UPDATED_BY,
434 CREATION_DATE,
435 CREATED_BY
436 )
437 SELECT
438 19,
439 'Sales Manager 1',
440 'Sales Manager Level 1',
441 'REP',
442 3,
443 sysdate,
444 1,
445 sysdate,
446 1
447 FROM dual
448 where not exists (
449 select 1
450 from msd_levels
451 where level_id = 19);
452
453
454 insert into msd_levels (
455 LEVEL_ID,
456 LEVEL_NAME,
457 DESCRIPTION,
458 DIMENSION_CODE,
459 LEVEL_TYPE_CODE,
460 LAST_UPDATE_DATE,
461 LAST_UPDATED_BY,
462 CREATION_DATE,
463 CREATED_BY
464 )
465 SELECT
466 20,
467 'Sales Manager 2',
468 'Sales Manager Level 2',
469 'REP',
470 3,
471 sysdate,
472 1,
473 sysdate,
474 1
475 FROM dual
476 where not exists (
477 select 1
478 from msd_levels
479 where level_id = 20);
480
481
482 insert into msd_levels (
483 LEVEL_ID,
484 LEVEL_NAME,
485 DESCRIPTION,
486 DIMENSION_CODE,
487 LEVEL_TYPE_CODE,
488 LAST_UPDATE_DATE,
489 LAST_UPDATED_BY,
490 CREATION_DATE,
491 CREATED_BY
492 )
493 SELECT
494 21,
495 'Sales Manager 3',
496 'Sales Manager Level 3',
497 'REP',
498 3,
499 sysdate,
500 1,
501 sysdate,
502 1
503 FROM dual
504 where not exists (
505 select 1
506 from msd_levels
507 where level_id = 21);
508
509
510 insert into msd_levels (
511 LEVEL_ID,
512 LEVEL_NAME,
513 DESCRIPTION,
514 DIMENSION_CODE,
515 LEVEL_TYPE_CODE,
516 LAST_UPDATE_DATE,
517 LAST_UPDATED_BY,
518 CREATION_DATE,
519 CREATED_BY
520 )
521 SELECT
522 22,
523 'Sales Manager 4',
524 'Sales Manager Level 4',
525 'REP',
526 3,
527 sysdate,
528 1,
529 sysdate,
530 1
531 FROM dual
532 where not exists (
533 select 1
534 from msd_levels
535 where level_id = 22);
536
537
538 insert into msd_levels (
539 LEVEL_ID,
540 LEVEL_NAME,
541 DESCRIPTION,
542 DIMENSION_CODE,
543 LEVEL_TYPE_CODE,
544 LAST_UPDATE_DATE,
545 LAST_UPDATED_BY,
546 CREATION_DATE,
547 CREATED_BY
548 )
549 SELECT
550 23,
551 'Sales Group 1',
552 'Sales Group Level 1',
553 'REP',
554 3,
555 sysdate,
556 1,
557 sysdate,
558 1
559 FROM dual
560 where not exists (
561 select 1
562 from msd_levels
563 where level_id = 23);
564
565
566 insert into msd_levels (
567 LEVEL_ID,
568 LEVEL_NAME,
569 DESCRIPTION,
570 DIMENSION_CODE,
571 LEVEL_TYPE_CODE,
572 LAST_UPDATE_DATE,
573 LAST_UPDATED_BY,
574 CREATION_DATE,
575 CREATED_BY
576 )
577 SELECT
578 24,
579 'Sales Group 2',
580 'Sales Group Level 2',
581 'REP',
582 3,
583 sysdate,
584 1,
585 sysdate,
586 1
587 FROM dual
588 where not exists (
589 select 1
590 from msd_levels
591 where level_id = 24);
592
593
594 insert into msd_levels (
595 LEVEL_ID,
596 LEVEL_NAME,
597 DESCRIPTION,
598 DIMENSION_CODE,
599 LEVEL_TYPE_CODE,
600 LAST_UPDATE_DATE,
601 LAST_UPDATED_BY,
602 CREATION_DATE,
603 CREATED_BY
604 )
605 SELECT
606 25,
607 'Sales Group 3',
608 'Sales Group Level 3',
609 'REP',
610 3,
611 sysdate,
612 1,
613 sysdate,
614 1
615 FROM dual
616 where not exists (
617 select 1
618 from msd_levels
619 where level_id = 25);
620
621
622 insert into msd_levels (
623 LEVEL_ID,
624 LEVEL_NAME,
625 DESCRIPTION,
626 DIMENSION_CODE,
627 LEVEL_TYPE_CODE,
628 LAST_UPDATE_DATE,
629 LAST_UPDATED_BY,
630 CREATION_DATE,
631 CREATED_BY
632 )
633 SELECT
634 26,
635 'Sales Group 4',
636 'Sales Group Level 4',
637 'REP',
638 3,
639 sysdate,
640 1,
641 sysdate,
642 1
643 FROM dual
644 where not exists (
645 select 1
646 from msd_levels
647 where level_id = 26);
648
649
650 insert into msd_levels (
651 LEVEL_ID,
652 LEVEL_NAME,
653 DESCRIPTION,
654 DIMENSION_CODE,
655 LEVEL_TYPE_CODE,
656 LAST_UPDATE_DATE,
657 LAST_UPDATED_BY,
658 CREATION_DATE,
659 CREATED_BY
660 )
661 SELECT
662 27,
663 'Sales Channel',
664 'Sales Channel Level',
665 'CHN',
666 2,
667 sysdate,
668 1,
669 sysdate,
670 1
671 FROM dual
672 where not exists (
673 select 1
674 from msd_levels
675 where level_id = 27);
676
677
678 insert into msd_levels (
679 LEVEL_ID,
680 LEVEL_NAME,
681 DESCRIPTION,
682 DIMENSION_CODE,
683 LEVEL_TYPE_CODE,
684 LAST_UPDATE_DATE,
685 LAST_UPDATED_BY,
686 CREATION_DATE,
687 CREATED_BY
688 )
689 SELECT
690 28,
691 'All Products',
692 'All Products Level',
693 'PRD',
694 1,
695 sysdate,
696 1,
697 sysdate,
698 1
699 FROM dual
700 where not exists (
701 select 1
702 from msd_levels
703 where level_id = 28);
704
705
706 insert into msd_levels (
707 LEVEL_ID,
708 LEVEL_NAME,
709 DESCRIPTION,
710 DIMENSION_CODE,
711 LEVEL_TYPE_CODE,
712 LAST_UPDATE_DATE,
713 LAST_UPDATED_BY,
714 CREATION_DATE,
715 CREATED_BY
716 )
717 SELECT
718 29,
719 'All Organization',
720 'All Organization Level',
721 'ORG',
722 1,
723 sysdate,
724 1,
728 where not exists (
725 sysdate,
726 1
727 FROM dual
729 select 1
730 from msd_levels
731 where level_id = 29);
732
733
734 insert into msd_levels (
735 LEVEL_ID,
736 LEVEL_NAME,
737 DESCRIPTION,
738 DIMENSION_CODE,
739 LEVEL_TYPE_CODE,
740 LAST_UPDATE_DATE,
741 LAST_UPDATED_BY,
742 CREATION_DATE,
743 CREATED_BY
744 )
745 SELECT
746 30,
747 'All Geography',
748 'All Geography Level',
749 'GEO',
750 1,
751 sysdate,
752 1,
753 sysdate,
754 1
755 FROM dual
756 where not exists (
757 select 1
758 from msd_levels
759 where level_id = 30);
760
761
762 insert into msd_levels (
763 LEVEL_ID,
764 LEVEL_NAME,
765 DESCRIPTION,
766 DIMENSION_CODE,
767 LEVEL_TYPE_CODE,
768 LAST_UPDATE_DATE,
769 LAST_UPDATED_BY,
770 CREATION_DATE,
771 CREATED_BY
772 )
773 SELECT
774 32,
775 'All Sales Representatives',
776 'All Sales Representatives Level',
777 'REP',
778 1,
779 sysdate,
780 1,
781 sysdate,
782 1
783 FROM dual
784 where not exists (
785 select 1
786 from msd_levels
787 where level_id = 32);
788
789
790 insert into msd_levels (
791 LEVEL_ID,
792 LEVEL_NAME,
793 DESCRIPTION,
794 DIMENSION_CODE,
795 LEVEL_TYPE_CODE,
796 LAST_UPDATE_DATE,
797 LAST_UPDATED_BY,
798 CREATION_DATE,
799 CREATED_BY
800 )
801 SELECT
802 33,
803 'All Sales Channels',
804 'All Sales Channels Level',
805 'CHN',
806 1,
807 sysdate,
808 1,
809 sysdate,
810 1
811 FROM dual
812 where not exists (
813 select 1
814 from msd_levels
815 where level_id = 33);
816
817 commit;
818 end;
819
820 PROCEDURE insert_hierarchies IS
821 begin
822
823 insert into msd_hierarchies(
824 HIERARCHY_ID,
825 HIERARCHY_NAME,
826 DESCRIPTION,
827 DIMENSION_CODE,
828 VALID_FLAG,
829 LAST_UPDATE_DATE,
830 LAST_UPDATED_BY,
831 CREATION_DATE,
832 CREATED_BY
833 )
834 SELECT
835 1,
836 'Product Category',
837 'Product Category Hierarchy',
838 'PRD',
839 1,
840 sysdate,
841 1,
842 sysdate,
843 1
844 FROM dual
845 where not exists (
846 select 1
847 from msd_hierarchies
848 where hierarchy_id = 1);
849
850
851 insert into msd_hierarchies(
852 HIERARCHY_ID,
853 HIERARCHY_NAME,
854 DESCRIPTION,
855 DIMENSION_CODE,
856 VALID_FLAG,
857 LAST_UPDATE_DATE,
858 LAST_UPDATED_BY,
859 CREATION_DATE,
860 CREATED_BY
861 )
862 SELECT
863 2,
864 'Product Family',
865 'Product Family Hierarchy',
866 'PRD',
867 1,
868 sysdate,
869 1,
870 sysdate,
871 1
872 FROM dual
873 where not exists (
874 select 1
875 from msd_hierarchies
876 where hierarchy_id = 2);
877
878
879 insert into msd_hierarchies(
880 HIERARCHY_ID,
881 HIERARCHY_NAME,
882 DESCRIPTION,
883 DIMENSION_CODE,
884 VALID_FLAG,
885 LAST_UPDATE_DATE,
886 LAST_UPDATED_BY,
887 CREATION_DATE,
888 CREATED_BY
889 )
890 SELECT
891 4,
892 'Organization',
893 'Organization Hierarchy',
894 'ORG',
895 1,
896 sysdate,
897 1,
898 sysdate,
899 1
900 FROM dual
901 where not exists (
902 select 1
903 from msd_hierarchies
904 where hierarchy_id = 4);
905
906
907 insert into msd_hierarchies(
908 HIERARCHY_ID,
909 HIERARCHY_NAME,
910 DESCRIPTION,
911 DIMENSION_CODE,
912 VALID_FLAG,
913 LAST_UPDATE_DATE,
914 LAST_UPDATED_BY,
915 CREATION_DATE,
916 CREATED_BY
917 )
918 SELECT
919 5,
920 'Geography',
921 'Geography Hierarchy',
922 'GEO',
923 1,
924 sysdate,
925 1,
926 sysdate,
927 1
928 FROM dual
929 where not exists (
930 select 1
931 from msd_hierarchies
932 where hierarchy_id = 5);
933
934
935 insert into msd_hierarchies(
936 HIERARCHY_ID,
937 HIERARCHY_NAME,
938 DESCRIPTION,
939 DIMENSION_CODE,
940 VALID_FLAG,
941 LAST_UPDATE_DATE,
942 LAST_UPDATED_BY,
943 CREATION_DATE,
944 CREATED_BY
945 )
946 SELECT
947 6,
948 'Customer Class',
949 'Customer Class Hierarchy',
950 'GEO',
951 1,
952 sysdate,
953 1,
954 sysdate,
958 select 1
955 1
956 FROM dual
957 where not exists (
959 from msd_hierarchies
960 where hierarchy_id = 6);
961
962
963 insert into msd_hierarchies(
964 HIERARCHY_ID,
965 HIERARCHY_NAME,
966 DESCRIPTION,
967 DIMENSION_CODE,
968 VALID_FLAG,
969 LAST_UPDATE_DATE,
970 LAST_UPDATED_BY,
971 CREATION_DATE,
972 CREATED_BY
973 )
974 SELECT
975 7,
976 'Customer Group',
977 'Customer Group Hierarchy',
978 'GEO',
979 1,
980 sysdate,
981 1,
982 sysdate,
983 1
984 FROM dual
985 where not exists (
986 select 1
987 from msd_hierarchies
988 where hierarchy_id = 7);
989
990
991 insert into msd_hierarchies(
992 HIERARCHY_ID,
993 HIERARCHY_NAME,
994 DESCRIPTION,
995 DIMENSION_CODE,
996 VALID_FLAG,
997 LAST_UPDATE_DATE,
998 LAST_UPDATED_BY,
999 CREATION_DATE,
1000 CREATED_BY
1001 )
1002 SELECT
1003 8,
1004 'Sales Representative',
1005 'Sales Rep Hierarchy',
1006 'REP',
1007 1,
1008 sysdate,
1009 1,
1010 sysdate,
1011 1
1012 FROM dual
1013 where not exists (
1014 select 1
1015 from msd_hierarchies
1016 where hierarchy_id = 8);
1017
1018
1019 insert into msd_hierarchies(
1020 HIERARCHY_ID,
1021 HIERARCHY_NAME,
1022 DESCRIPTION,
1023 DIMENSION_CODE,
1024 VALID_FLAG,
1025 LAST_UPDATE_DATE,
1026 LAST_UPDATED_BY,
1027 CREATION_DATE,
1028 CREATED_BY
1029 )
1030 SELECT
1031 9,
1032 'Sales Group',
1033 'Sales Group Hierarchy',
1034 'REP',
1035 1,
1036 sysdate,
1037 1,
1038 sysdate,
1039 1
1040 FROM dual
1041 where not exists (
1042 select 1
1043 from msd_hierarchies
1044 where hierarchy_id = 9);
1045
1046
1047 insert into msd_hierarchies(
1048 HIERARCHY_ID,
1049 HIERARCHY_NAME,
1050 DESCRIPTION,
1051 DIMENSION_CODE,
1052 VALID_FLAG,
1053 LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY,
1055 CREATION_DATE,
1056 CREATED_BY
1057 )
1058 SELECT
1059 10,
1060 'Sales Channel',
1061 'Sales Channel Hierarhcy',
1062 'CHN',
1063 1,
1064 sysdate,
1065 1,
1066 sysdate,
1067 1
1068 FROM dual
1069 where not exists (
1070 select 1
1071 from msd_hierarchies
1072 where hierarchy_id = 10);
1073
1074 commit;
1075
1076 end;
1077
1078 PROCEDURE insert_hierarchy_levels IS
1079 begin
1080
1081 insert into msd_hierarchy_levels (
1082 HIERARCHY_ID,
1083 LEVEL_ID,
1084 PARENT_LEVEL_ID,
1085 RELATIONSHIP_VIEW,
1086 level_value_column,level_value_pk_column,
1087 parent_value_column,parent_value_pk_column,
1088 LAST_UPDATE_DATE,
1089 LAST_UPDATED_BY,
1090 CREATION_DATE,
1091 CREATED_BY
1092 )
1093 SELECT
1094 1,
1095 1,
1096 2,
1097 'MSD_SR_PRD_CAT_V',
1098 'LEVEL_VALUE','LEVEL_VALUE_PK',
1099 'PARENT_VALUE','PARENT_VALUE_PK',
1100 sysdate,
1101 1,
1102 sysdate,
1103 1
1104 FROM dual
1105 WHERE not exists (
1106 select 1
1107 from msd_hierarchy_levels
1108 where hierarchy_id = 1
1109 and level_id = 1
1110 and parent_level_id = 2);
1111
1112
1113 insert into msd_hierarchy_levels (
1114 HIERARCHY_ID,
1115 LEVEL_ID,
1116 PARENT_LEVEL_ID,
1117 RELATIONSHIP_VIEW,
1118 level_value_column,level_value_pk_column,
1119 parent_value_column,parent_value_pk_column,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 CREATION_DATE,
1123 CREATED_BY
1124 )
1125 SELECT
1126 1,
1127 2,
1128 28,
1129 'MSD_SR_CAT_ALL_V',
1130 'LEVEL_VALUE','LEVEL_VALUE_PK',
1131 'PARENT_VALUE','PARENT_VALUE_PK',
1132 sysdate,
1133 1,
1134 sysdate,
1135 1
1136 FROM dual
1137 WHERE not exists (
1138 select 1
1139 from msd_hierarchy_levels
1140 where hierarchy_id = 1
1141 and level_id = 2
1142 and parent_level_id = 28);
1143
1144
1145 insert into msd_hierarchy_levels (
1146 HIERARCHY_ID,
1147 LEVEL_ID,
1148 PARENT_LEVEL_ID,
1149 RELATIONSHIP_VIEW,
1150 level_value_column,level_value_pk_column,
1151 parent_value_column,parent_value_pk_column,
1152 LAST_UPDATE_DATE,
1153 LAST_UPDATED_BY,
1154 CREATION_DATE,
1155 CREATED_BY
1156 )
1157 SELECT
1158 2,
1159 1,
1160 3,
1161 'MSD_SR_PRD_PF_V',
1162 'LEVEL_VALUE','LEVEL_VALUE_PK',
1163 'PARENT_VALUE','PARENT_VALUE_PK',
1164 sysdate,
1165 1,
1166 sysdate,
1167 1
1168 FROM dual
1169 WHERE not exists (
1170 select 1
1171 from msd_hierarchy_levels
1175
1172 where hierarchy_id = 2
1173 and level_id = 1
1174 and parent_level_id = 3);
1176
1177 insert into msd_hierarchy_levels (
1178 HIERARCHY_ID,
1179 LEVEL_ID,
1180 PARENT_LEVEL_ID,
1181 RELATIONSHIP_VIEW,
1182 level_value_column,level_value_pk_column,
1183 parent_value_column,parent_value_pk_column,
1184 LAST_UPDATE_DATE,
1185 LAST_UPDATED_BY,
1186 CREATION_DATE,
1187 CREATED_BY
1188 )
1189 SELECT
1190 2,
1191 3,
1192 28,
1193 'MSD_SR_PF_ALL_V',
1194 'LEVEL_VALUE','LEVEL_VALUE_PK',
1195 'PARENT_VALUE','PARENT_VALUE_PK',
1196 sysdate,
1197 1,
1198 sysdate,
1199 1
1200 FROM dual
1201 WHERE not exists (
1202 select 1
1203 from msd_hierarchy_levels
1204 where hierarchy_id = 2
1205 and level_id = 3
1206 and parent_level_id = 28);
1207
1208
1209 insert into msd_hierarchy_levels (
1210 HIERARCHY_ID,
1211 LEVEL_ID,
1212 PARENT_LEVEL_ID,
1213 RELATIONSHIP_VIEW,
1214 level_value_column,level_value_pk_column,
1215 parent_value_column,parent_value_pk_column,
1216 LAST_UPDATE_DATE,
1217 LAST_UPDATED_BY,
1218 CREATION_DATE,
1219 CREATED_BY
1220 )
1221 SELECT
1222 4,
1223 7,
1224 8,
1225 'MSD_SR_ORG_OU_V',
1226 'LEVEL_VALUE','LEVEL_VALUE_PK',
1227 'PARENT_VALUE','PARENT_VALUE_PK',
1228 sysdate,
1229 1,
1230 sysdate,
1231 1
1232 FROM dual
1233 WHERE not exists (
1234 select 1
1235 from msd_hierarchy_levels
1236 where hierarchy_id = 4
1237 and level_id = 7
1238 and parent_level_id = 8);
1239
1240
1241 insert into msd_hierarchy_levels (
1242 HIERARCHY_ID,
1243 LEVEL_ID,
1244 PARENT_LEVEL_ID,
1245 RELATIONSHIP_VIEW,
1246 level_value_column,level_value_pk_column,
1247 parent_value_column,parent_value_pk_column,
1248 LAST_UPDATE_DATE,
1249 LAST_UPDATED_BY,
1250 CREATION_DATE,
1251 CREATED_BY
1252 )
1253 SELECT
1254 4,
1255 8,
1256 9,
1257 'MSD_SR_OU_LE_V',
1258 'LEVEL_VALUE','LEVEL_VALUE_PK',
1259 'PARENT_VALUE','PARENT_VALUE_PK',
1260 sysdate,
1261 1,
1262 sysdate,
1263 1
1264 FROM dual
1265 WHERE not exists (
1266 select 1
1267 from msd_hierarchy_levels
1268 where hierarchy_id = 4
1269 and level_id = 8
1270 and parent_level_id = 9);
1271
1272
1273 insert into msd_hierarchy_levels (
1274 HIERARCHY_ID,
1275 LEVEL_ID,
1276 PARENT_LEVEL_ID,
1277 RELATIONSHIP_VIEW,
1278 level_value_column,level_value_pk_column,
1279 parent_value_column,parent_value_pk_column,
1280 LAST_UPDATE_DATE,
1281 LAST_UPDATED_BY,
1282 CREATION_DATE,
1283 CREATED_BY
1284 )
1285 SELECT
1286 4,
1287 9,
1288 10,
1289 'MSD_SR_LE_BG_V',
1290 'LEVEL_VALUE','LEVEL_VALUE_PK',
1291 'PARENT_VALUE','PARENT_VALUE_PK',
1292 sysdate,
1293 1,
1294 sysdate,
1295 1
1296 FROM dual
1297 WHERE not exists (
1298 select 1
1299 from msd_hierarchy_levels
1300 where hierarchy_id = 4
1301 and level_id = 9
1302 and parent_level_id = 10);
1303
1304
1305 insert into msd_hierarchy_levels (
1306 HIERARCHY_ID,
1307 LEVEL_ID,
1308 PARENT_LEVEL_ID,
1309 RELATIONSHIP_VIEW,
1310 level_value_column,level_value_pk_column,
1311 parent_value_column,parent_value_pk_column,
1312 LAST_UPDATE_DATE,
1313 LAST_UPDATED_BY,
1314 CREATION_DATE,
1315 CREATED_BY
1316 )
1317 SELECT
1318 4,
1319 10,
1320 29,
1321 'MSD_SR_BG_ALL_V',
1322 'LEVEL_VALUE','LEVEL_VALUE_PK',
1323 'PARENT_VALUE','PARENT_VALUE_PK',
1324 sysdate,
1325 1,
1326 sysdate,
1327 1
1328 FROM dual
1329 WHERE not exists (
1330 select 1
1331 from msd_hierarchy_levels
1332 where hierarchy_id = 4
1333 and level_id = 10
1334 and parent_level_id = 29);
1335
1336
1337 insert into msd_hierarchy_levels (
1338 HIERARCHY_ID,
1339 LEVEL_ID,
1340 PARENT_LEVEL_ID,
1341 RELATIONSHIP_VIEW,
1342 level_value_column,level_value_pk_column,
1343 parent_value_column,parent_value_pk_column,
1344 LAST_UPDATE_DATE,
1345 LAST_UPDATED_BY,
1346 CREATION_DATE,
1347 CREATED_BY
1348 )
1349 SELECT
1350 5,
1351 11,
1352 12,
1353 'MSD_SR_LOC_REG_V',
1354 'LEVEL_VALUE','LEVEL_VALUE_PK',
1355 'PARENT_VALUE','PARENT_VALUE_PK',
1356 sysdate,
1357 1,
1358 sysdate,
1359 1
1360 FROM dual
1361 WHERE not exists (
1362 select 1
1363 from msd_hierarchy_levels
1364 where hierarchy_id = 5
1365 and level_id = 11
1366 and parent_level_id = 12);
1367
1368
1369 insert into msd_hierarchy_levels (
1370 HIERARCHY_ID,
1371 LEVEL_ID,
1372 PARENT_LEVEL_ID,
1376 LAST_UPDATE_DATE,
1373 RELATIONSHIP_VIEW,
1374 level_value_column,level_value_pk_column,
1375 parent_value_column,parent_value_pk_column,
1377 LAST_UPDATED_BY,
1378 CREATION_DATE,
1379 CREATED_BY
1380 )
1381 SELECT
1382 5,
1383 12,
1384 13,
1385 'MSD_SR_REG_COUNTRY_V',
1386 'LEVEL_VALUE','LEVEL_VALUE_PK',
1387 'PARENT_VALUE','PARENT_VALUE_PK',
1388 sysdate,
1389 1,
1390 sysdate,
1391 1
1392 FROM dual
1393 WHERE not exists (
1394 select 1
1395 from msd_hierarchy_levels
1396 where hierarchy_id = 5
1397 and level_id = 12
1398 and parent_level_id = 13);
1399
1400
1401 insert into msd_hierarchy_levels (
1402 HIERARCHY_ID,
1403 LEVEL_ID,
1404 PARENT_LEVEL_ID,
1405 RELATIONSHIP_VIEW,
1406 level_value_column,level_value_pk_column,
1407 parent_value_column,parent_value_pk_column,
1408 LAST_UPDATE_DATE,
1409 LAST_UPDATED_BY,
1410 CREATION_DATE,
1411 CREATED_BY
1412 )
1413 SELECT
1414 5,
1415 13,
1416 14,
1417 'MSD_SR_COUNTRY_AREA_V',
1418 'LEVEL_VALUE','LEVEL_VALUE_PK',
1419 'PARENT_VALUE','PARENT_VALUE_PK',
1420 sysdate,
1421 1,
1422 sysdate,
1423 1
1424 FROM dual
1425 WHERE not exists (
1426 select 1
1427 from msd_hierarchy_levels
1428 where hierarchy_id = 5
1429 and level_id = 13
1430 and parent_level_id = 14);
1431
1432
1433 insert into msd_hierarchy_levels (
1434 HIERARCHY_ID,
1435 LEVEL_ID,
1436 PARENT_LEVEL_ID,
1437 RELATIONSHIP_VIEW,
1438 level_value_column,level_value_pk_column,
1439 parent_value_column,parent_value_pk_column,
1440 LAST_UPDATE_DATE,
1441 LAST_UPDATED_BY,
1442 CREATION_DATE,
1443 CREATED_BY
1444 )
1445 SELECT
1446 5,
1447 14,
1448 30,
1449 'MSD_SR_AREA_ALL_V',
1450 'LEVEL_VALUE','LEVEL_VALUE_PK',
1451 'PARENT_VALUE','PARENT_VALUE_PK',
1452 sysdate,
1453 1,
1454 sysdate,
1455 1
1456 FROM dual
1457 WHERE not exists (
1458 select 1
1459 from msd_hierarchy_levels
1460 where hierarchy_id = 5
1461 and level_id = 14
1462 and parent_level_id = 30);
1463
1464
1465 insert into msd_hierarchy_levels (
1466 HIERARCHY_ID,
1467 LEVEL_ID,
1468 PARENT_LEVEL_ID,
1469 RELATIONSHIP_VIEW,
1470 level_value_column,level_value_pk_column,
1471 parent_value_column,parent_value_pk_column,
1472 LAST_UPDATE_DATE,
1473 LAST_UPDATED_BY,
1474 CREATION_DATE,
1475 CREATED_BY
1476 )
1477 SELECT
1478 6,
1479 11,
1480 15,
1481 'MSD_SR_LOC_CUS_V',
1482 'LEVEL_VALUE','LEVEL_VALUE_PK',
1483 'PARENT_VALUE','PARENT_VALUE_PK',
1484 sysdate,
1485 1,
1486 sysdate,
1487 1
1488 FROM dual
1489 WHERE not exists (
1490 select 1
1491 from msd_hierarchy_levels
1492 where hierarchy_id = 6
1493 and level_id = 11
1494 and parent_level_id = 15);
1495
1496
1497 insert into msd_hierarchy_levels (
1498 HIERARCHY_ID,
1499 LEVEL_ID,
1500 PARENT_LEVEL_ID,
1501 RELATIONSHIP_VIEW,
1502 level_value_column,level_value_pk_column,
1503 parent_value_column,parent_value_pk_column,
1504 LAST_UPDATE_DATE,
1505 LAST_UPDATED_BY,
1506 CREATION_DATE,
1507 CREATED_BY
1508 )
1509 SELECT
1510 6,
1511 15,
1512 16,
1513 'MSD_SR_CUS_CLASS_V',
1514 'LEVEL_VALUE','LEVEL_VALUE_PK',
1515 'PARENT_VALUE','PARENT_VALUE_PK',
1516 sysdate,
1517 1,
1518 sysdate,
1519 1
1520 FROM dual
1521 WHERE not exists (
1522 select 1
1523 from msd_hierarchy_levels
1524 where hierarchy_id = 6
1525 and level_id = 15
1526 and parent_level_id = 16);
1527
1528
1529 insert into msd_hierarchy_levels (
1530 HIERARCHY_ID,
1531 LEVEL_ID,
1532 PARENT_LEVEL_ID,
1533 RELATIONSHIP_VIEW,
1534 level_value_column,level_value_pk_column,
1535 parent_value_column,parent_value_pk_column,
1536 LAST_UPDATE_DATE,
1537 LAST_UPDATED_BY,
1538 CREATION_DATE,
1539 CREATED_BY
1540 )
1541 SELECT
1542 6,
1543 16,
1544 30,
1545 'MSD_SR_CLASS_ALL_V',
1546 'LEVEL_VALUE','LEVEL_VALUE_PK',
1547 'PARENT_VALUE','PARENT_VALUE_PK',
1548 sysdate,
1549 1,
1550 sysdate,
1551 1
1552 FROM dual
1553 WHERE not exists (
1554 select 1
1555 from msd_hierarchy_levels
1556 where hierarchy_id = 6
1557 and level_id = 16
1558 and parent_level_id = 30);
1559
1560
1561 /* zia 6/21/01:
1562 Bug fix for older seed data which had customer class views instead
1563 of customer group views
1564 */
1565 UPDATE msd_hierarchy_levels
1566 SET relationship_view = 'MSD_SR_CUS_CLASS_V'
1567 where hierarchy_id = 6
1568 and level_id = 15
1569 and parent_level_id = 16
1570 and relationship_view = 'MSD_SR_CUS_GROUP_V';
1571
1572 UPDATE msd_hierarchy_levels
1576 and parent_level_id = 30
1573 SET relationship_view = 'MSD_SR_CLASS_ALL_V'
1574 where hierarchy_id = 6
1575 and level_id = 16
1577 and relationship_view = 'MSD_SR_GROUP_ALL_V';
1578 /* zia 6/21/01: end */
1579
1580
1581 insert into msd_hierarchy_levels (
1582 HIERARCHY_ID,
1583 LEVEL_ID,
1584 PARENT_LEVEL_ID,
1585 RELATIONSHIP_VIEW,
1586 level_value_column,level_value_pk_column,
1587 parent_value_column,parent_value_pk_column,
1588 LAST_UPDATE_DATE,
1589 LAST_UPDATED_BY,
1590 CREATION_DATE,
1591 CREATED_BY
1592 )
1593 SELECT
1594 7,
1595 11,
1596 15,
1597 'MSD_SR_LOC_CUS_V',
1598 'LEVEL_VALUE','LEVEL_VALUE_PK',
1599 'PARENT_VALUE','PARENT_VALUE_PK',
1600 sysdate,
1601 1,
1602 sysdate,
1603 1
1604 FROM dual
1605 WHERE not exists (
1606 select 1
1607 from msd_hierarchy_levels
1608 where hierarchy_id = 7
1609 and level_id = 11
1610 and parent_level_id = 15);
1611
1612
1613 insert into msd_hierarchy_levels (
1614 HIERARCHY_ID,
1615 LEVEL_ID,
1616 PARENT_LEVEL_ID,
1617 RELATIONSHIP_VIEW,
1618 level_value_column,level_value_pk_column,
1619 parent_value_column,parent_value_pk_column,
1620 LAST_UPDATE_DATE,
1621 LAST_UPDATED_BY,
1622 CREATION_DATE,
1623 CREATED_BY
1624 )
1625 SELECT
1626 7,
1627 15,
1628 17,
1629 'MSD_SR_CUS_GROUP_V',
1630 'LEVEL_VALUE','LEVEL_VALUE_PK',
1631 'PARENT_VALUE','PARENT_VALUE_PK',
1632 sysdate,
1633 1,
1634 sysdate,
1635 1
1636 FROM dual
1637 WHERE not exists (
1638 select 1
1639 from msd_hierarchy_levels
1640 where hierarchy_id = 7
1641 and level_id = 15
1642 and parent_level_id = 17);
1643
1644
1645 insert into msd_hierarchy_levels (
1646 HIERARCHY_ID,
1647 LEVEL_ID,
1648 PARENT_LEVEL_ID,
1649 RELATIONSHIP_VIEW,
1650 level_value_column,level_value_pk_column,
1651 parent_value_column,parent_value_pk_column,
1652 LAST_UPDATE_DATE,
1653 LAST_UPDATED_BY,
1654 CREATION_DATE,
1655 CREATED_BY
1656 )
1657 SELECT
1658 7,
1659 17,
1660 30,
1661 'MSD_SR_GROUP_ALL_V',
1662 'LEVEL_VALUE','LEVEL_VALUE_PK',
1663 'PARENT_VALUE','PARENT_VALUE_PK',
1664 sysdate,
1665 1,
1666 sysdate,
1667 1
1668 FROM dual
1669 WHERE not exists (
1670 select 1
1671 from msd_hierarchy_levels
1672 where hierarchy_id = 7
1673 and level_id = 17
1674 and parent_level_id = 30);
1675
1676
1677 /* zia 6/21/01:
1678 Bug fix for older seed data which had customer class views instead
1679 of customer group views
1680 */
1681 UPDATE msd_hierarchy_levels
1682 SET relationship_view = 'MSD_SR_CUS_GROUP_V'
1683 where hierarchy_id = 7
1684 and level_id = 15
1685 and parent_level_id = 17
1686 and relationship_view = 'MSD_SR_CUS_CLASS_V';
1687
1688 UPDATE msd_hierarchy_levels
1689 SET relationship_view = 'MSD_SR_GROUP_ALL_V'
1690 where hierarchy_id = 7
1691 and level_id = 17
1692 and parent_level_id = 30
1693 and relationship_view = 'MSD_SR_CLASS_ALL_V';
1694 /* zia 6/21/01: end */
1695
1696
1697 insert into msd_hierarchy_levels (
1698 HIERARCHY_ID,
1699 LEVEL_ID,
1700 PARENT_LEVEL_ID,
1701 RELATIONSHIP_VIEW,
1702 level_value_column,level_value_pk_column,
1703 parent_value_column,parent_value_pk_column,
1704 LAST_UPDATE_DATE,
1705 LAST_UPDATED_BY,
1709 SELECT
1706 CREATION_DATE,
1707 CREATED_BY
1708 )
1710 8,
1711 18,
1712 19,
1713 'MSD_SR_REP_MGR1_V',
1714 'LEVEL_VALUE','LEVEL_VALUE_PK',
1715 'PARENT_VALUE','PARENT_VALUE_PK',
1716 sysdate,
1717 1,
1718 sysdate,
1719 1
1720 FROM dual
1721 WHERE not exists (
1722 select 1
1723 from msd_hierarchy_levels
1724 where hierarchy_id = 8
1725 and level_id = 18
1726 and parent_level_id = 19);
1727
1728
1729 insert into msd_hierarchy_levels (
1730 HIERARCHY_ID,
1731 LEVEL_ID,
1732 PARENT_LEVEL_ID,
1733 RELATIONSHIP_VIEW,
1734 level_value_column,level_value_pk_column,
1735 parent_value_column,parent_value_pk_column,
1736 LAST_UPDATE_DATE,
1737 LAST_UPDATED_BY,
1738 CREATION_DATE,
1739 CREATED_BY
1740 )
1741 SELECT
1742 8,
1743 19,
1744 20,
1745 'MSD_SR_MGR1_MGR2_V',
1746 'LEVEL_VALUE','LEVEL_VALUE_PK',
1747 'PARENT_VALUE','PARENT_VALUE_PK',
1748 sysdate,
1749 1,
1750 sysdate,
1751 1
1752 FROM dual
1753 WHERE not exists (
1754 select 1
1755 from msd_hierarchy_levels
1756 where hierarchy_id = 8
1757 and level_id = 19
1758 and parent_level_id = 20);
1759
1760
1761 insert into msd_hierarchy_levels (
1762 HIERARCHY_ID,
1763 LEVEL_ID,
1764 PARENT_LEVEL_ID,
1765 RELATIONSHIP_VIEW,
1766 level_value_column,level_value_pk_column,
1767 parent_value_column,parent_value_pk_column,
1768 LAST_UPDATE_DATE,
1769 LAST_UPDATED_BY,
1770 CREATION_DATE,
1771 CREATED_BY
1772 )
1773 SELECT
1774 8,
1775 20,
1776 21,
1777 'MSD_SR_MGR2_MGR3_V',
1778 'LEVEL_VALUE','LEVEL_VALUE_PK',
1779 'PARENT_VALUE','PARENT_VALUE_PK',
1780 sysdate,
1781 1,
1782 sysdate,
1783 1
1784 FROM dual
1785 WHERE not exists (
1786 select 1
1787 from msd_hierarchy_levels
1788 where hierarchy_id = 8
1789 and level_id = 20
1790 and parent_level_id = 21);
1791
1792
1793 insert into msd_hierarchy_levels (
1794 HIERARCHY_ID,
1795 LEVEL_ID,
1796 PARENT_LEVEL_ID,
1797 RELATIONSHIP_VIEW,
1798 level_value_column,level_value_pk_column,
1799 parent_value_column,parent_value_pk_column,
1800 LAST_UPDATE_DATE,
1801 LAST_UPDATED_BY,
1802 CREATION_DATE,
1803 CREATED_BY
1804 )
1805 SELECT
1806 8,
1807 21,
1808 22,
1809 'MSD_SR_MGR3_MGR4_V',
1810 'LEVEL_VALUE','LEVEL_VALUE_PK',
1811 'PARENT_VALUE','PARENT_VALUE_PK',
1812 sysdate,
1813 1,
1814 sysdate,
1815 1
1816 FROM dual
1817 WHERE not exists (
1818 select 1
1819 from msd_hierarchy_levels
1820 where hierarchy_id = 8
1821 and level_id = 21
1822 and parent_level_id = 22);
1823
1824
1825 insert into msd_hierarchy_levels (
1826 HIERARCHY_ID,
1830 level_value_column,level_value_pk_column,
1827 LEVEL_ID,
1828 PARENT_LEVEL_ID,
1829 RELATIONSHIP_VIEW,
1831 parent_value_column,parent_value_pk_column,
1832 LAST_UPDATE_DATE,
1833 LAST_UPDATED_BY,
1834 CREATION_DATE,
1835 CREATED_BY
1836 )
1837 SELECT
1838 8,
1839 22,
1840 32,
1841 'MSD_SR_MGR4_ALL_V',
1842 'LEVEL_VALUE','LEVEL_VALUE_PK',
1843 'PARENT_VALUE','PARENT_VALUE_PK',
1844 sysdate,
1845 1,
1846 sysdate,
1847 1
1848 FROM dual
1849 WHERE not exists (
1850 select 1
1851 from msd_hierarchy_levels
1852 where hierarchy_id = 8
1853 and level_id = 22
1854 and parent_level_id = 32);
1855
1856
1857 insert into msd_hierarchy_levels (
1858 HIERARCHY_ID,
1859 LEVEL_ID,
1860 PARENT_LEVEL_ID,
1861 RELATIONSHIP_VIEW,
1862 level_value_column,level_value_pk_column,
1863 parent_value_column,parent_value_pk_column,
1864 LAST_UPDATE_DATE,
1865 LAST_UPDATED_BY,
1866 CREATION_DATE,
1867 CREATED_BY
1868 )
1869 SELECT
1870 9,
1871 18,
1872 23,
1873 'MSD_SR_REP_GRP1_V',
1874 'LEVEL_VALUE','LEVEL_VALUE_PK',
1875 'PARENT_VALUE','PARENT_VALUE_PK',
1876 sysdate,
1877 1,
1878 sysdate,
1879 1
1880 FROM dual
1881 WHERE not exists (
1882 select 1
1883 from msd_hierarchy_levels
1884 where hierarchy_id = 9
1885 and level_id = 18
1886 and parent_level_id = 23);
1887
1888
1889 insert into msd_hierarchy_levels (
1890 HIERARCHY_ID,
1891 LEVEL_ID,
1892 PARENT_LEVEL_ID,
1893 RELATIONSHIP_VIEW,
1894 level_value_column,level_value_pk_column,
1895 parent_value_column,parent_value_pk_column,
1896 LAST_UPDATE_DATE,
1897 LAST_UPDATED_BY,
1898 CREATION_DATE,
1899 CREATED_BY
1900 )
1901 SELECT
1902 9,
1903 23,
1904 24,
1905 'MSD_SR_GRP1_GRP2_V',
1906 'LEVEL_VALUE','LEVEL_VALUE_PK',
1907 'PARENT_VALUE','PARENT_VALUE_PK',
1908 sysdate,
1909 1,
1910 sysdate,
1911 1
1912 FROM dual
1913 WHERE not exists (
1914 select 1
1915 from msd_hierarchy_levels
1916 where hierarchy_id = 9
1917 and level_id = 23
1918 and parent_level_id = 24);
1919
1920
1921 insert into msd_hierarchy_levels (
1922 HIERARCHY_ID,
1923 LEVEL_ID,
1924 PARENT_LEVEL_ID,
1925 RELATIONSHIP_VIEW,
1926 level_value_column,level_value_pk_column,
1927 parent_value_column,parent_value_pk_column,
1928 LAST_UPDATE_DATE,
1929 LAST_UPDATED_BY,
1930 CREATION_DATE,
1931 CREATED_BY
1932 )
1933 SELECT
1934 9,
1935 24,
1936 25,
1937 'MSD_SR_GRP2_GRP3_V',
1938 'LEVEL_VALUE','LEVEL_VALUE_PK',
1939 'PARENT_VALUE','PARENT_VALUE_PK',
1940 sysdate,
1941 1,
1942 sysdate,
1943 1
1944 FROM dual
1945 WHERE not exists (
1946 select 1
1947 from msd_hierarchy_levels
1948 where hierarchy_id = 9
1949 and level_id = 24
1950 and parent_level_id = 25);
1951
1952
1953 insert into msd_hierarchy_levels (
1954 HIERARCHY_ID,
1955 LEVEL_ID,
1956 PARENT_LEVEL_ID,
1957 RELATIONSHIP_VIEW,
1958 level_value_column,level_value_pk_column,
1959 parent_value_column,parent_value_pk_column,
1960 LAST_UPDATE_DATE,
1961 LAST_UPDATED_BY,
1962 CREATION_DATE,
1963 CREATED_BY
1964 )
1965 SELECT
1966 9,
1967 25,
1968 26,
1969 'MSD_SR_GRP3_GRP4_V',
1970 'LEVEL_VALUE','LEVEL_VALUE_PK',
1971 'PARENT_VALUE','PARENT_VALUE_PK',
1972 sysdate,
1973 1,
1974 sysdate,
1975 1
1976 FROM dual
1977 WHERE not exists (
1978 select 1
1979 from msd_hierarchy_levels
1980 where hierarchy_id = 9
1981 and level_id = 25
1982 and parent_level_id = 26);
1983
1984
1985 insert into msd_hierarchy_levels (
1986 HIERARCHY_ID,
1987 LEVEL_ID,
1988 PARENT_LEVEL_ID,
1989 RELATIONSHIP_VIEW,
1990 level_value_column,level_value_pk_column,
1991 parent_value_column,parent_value_pk_column,
1992 LAST_UPDATE_DATE,
1993 LAST_UPDATED_BY,
1994 CREATION_DATE,
1995 CREATED_BY
1996 )
1997 SELECT
1998 9,
1999 26,
2000 32,
2001 'MSD_SR_GRP4_ALL_V',
2002 'LEVEL_VALUE','LEVEL_VALUE_PK',
2003 'PARENT_VALUE','PARENT_VALUE_PK',
2004 sysdate,
2005 1,
2006 sysdate,
2007 1
2008 FROM dual
2009 WHERE not exists (
2010 select 1
2011 from msd_hierarchy_levels
2012 where hierarchy_id = 9
2013 and level_id = 26
2014 and parent_level_id = 32);
2015
2016
2017 insert into msd_hierarchy_levels (
2018 HIERARCHY_ID,
2019 LEVEL_ID,
2020 PARENT_LEVEL_ID,
2021 RELATIONSHIP_VIEW,
2022 level_value_column,level_value_pk_column,
2023 parent_value_column,parent_value_pk_column,
2024 LAST_UPDATE_DATE,
2025 LAST_UPDATED_BY,
2026 CREATION_DATE,
2027 CREATED_BY
2028 )
2029 SELECT
2030 10,
2031 27,
2032 33,
2033 'MSD_SR_SC_ALL_V',
2034 'LEVEL_VALUE','LEVEL_VALUE_PK',
2035 'PARENT_VALUE','PARENT_VALUE_PK',
2036 sysdate,
2037 1,
2038 sysdate,
2039 1
2040 FROM dual
2041 WHERE not exists (
2042 select 1
2043 from msd_hierarchy_levels
2044 where hierarchy_id = 10
2045 and level_id = 27
2046 and parent_level_id = 33);
2047
2048
2049 commit;
2050
2051 end;
2052
2053 PROCEDURE insert_all IS
2054 begin
2055 insert_hierarchies;
2056 insert_levels;
2057 insert_hierarchy_levels;
2058 end;
2059
2060 END MSD_SEED;