[Home] [Help]
PACKAGE BODY: APPS.MTH_ITEM_DIMENSION_PKG
Source
1 Package Body mth_item_dimension_pkg AS
2 /*$Header: mthitemb.pls 120.1.12010000.2 2008/09/09 00:51:07 tkan ship $ */
3
4 /* ****************************************************************************
5 * Procedure :ITEM_DIM_LOAD_DENORM *
6 * Description :This procedure is used to populate the denorm table *
7 * for the item dimension hierarchy *
8 * File Name :MTHITEMDB.PLS *
9 * Visibility :Public *
10 * Parameters : *
11 * Modification log : *
12 * Author Date Change *
13 * Ankit Goyal 29-May--2007 Initial Creation *
14 **************************************************************************** */
15 PROCEDURE ITEM_DIM_LOAD_DENORM
16 IS
17 v_unassigned_key number;
18 v_unassigned_item_name varchar2(240);
19 v_unassigned_category_name varchar2(240);
20 BEGIN
21
22 --insert the values in the denormalised item denorm table.
23 --use of max function in the select is just to by pass the usage in the
24 --group by clause.
25 --Maximun nuumber of leves supported is 10.
26 --Item will be at lowest level i.e. 10.
27
28 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
29
30 BEGIN
31 SELECT ITEM_NAME into v_unassigned_item_name
32 from MTH_ITEMS_D
33 where item_PK_KEY = v_unassigned_key;
34 exception
35 when TOO_MANY_ROWS then
36 v_unassigned_item_name := null;
37 when NO_DATA_FOUND then
38 v_unassigned_item_name := null;
39 when others then
40 v_unassigned_item_name := null;
41 end;
42
43 BEGIN
44 select CATEGORY_NAME into v_unassigned_category_name
45 from MTH_ITEM_CATEGORIES_D
46 where CATEGORY_PK_KEY = v_unassigned_key;
47 exception
48 when TOO_MANY_ROWS then
49 v_unassigned_item_name := null;
50 when NO_DATA_FOUND then
51 v_unassigned_item_name := null;
52 when others then
53 v_unassigned_item_name := null;
54 end;
55
56 INSERT
57 INTO MTH_ITEM_DENORM_D
58 (
59 HIERARCHY_ID,
60 ITEM_FK_KEY,
61 LEVEL1_FK_KEY,
62 LEVEL2_FK_KEY,
63 LEVEL3_FK_KEY,
64 LEVEL4_FK_KEY,
65 LEVEL5_FK_KEY,
66 LEVEL6_FK_KEY,
67 LEVEL7_FK_KEY,
68 LEVEL8_FK_KEY,
69 LEVEL9_FK_KEY,
70 LEVEL_NUM,
71 ITEM_NAME,
72 LEVEL1_NAME,
73 LEVEL2_NAME,
74 LEVEL3_NAME,
75 LEVEL4_NAME,
76 LEVEL5_NAME,
77 LEVEL6_NAME,
78 LEVEL7_NAME,
79 LEVEL8_NAME,
80 LEVEL9_NAME
81 )
82 SELECT HIERARCHY_ID,
83 ITEM_FK_KEY,
84 LEVEL1_FK_KEY,
85 LEVEL2_FK_KEY,
86 LEVEL3_FK_KEY,
87 LEVEL4_FK_KEY,
88 LEVEL5_FK_KEY,
89 LEVEL6_FK_KEY,
90 LEVEL7_FK_KEY,
91 LEVEL8_FK_KEY,
92 LEVEL9_FK_KEY,
93 LEVEL_NUM,
94 ITEM_NAME,
95 LEVEL1_NAME,
96 LEVEL2_NAME,
97 LEVEL3_NAME,
98 LEVEL4_NAME,
99 LEVEL5_NAME,
100 LEVEL6_NAME,
101 LEVEL7_NAME,
102 LEVEL8_NAME,
103 LEVEL9_NAME
104 FROM (SELECT HIERARCHY_ID,
105 ITEM_PK_KEY ITEM_FK_KEY,
106 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
107 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
108 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
109 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
110 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
111 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
112 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
113 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
114 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
115 MAX(LEVEL_NUM) LEVEL_NUM,
116 MAX(ITEM_NAME) ITEM_NAME,
117 MAX(LEVEL1_NAME) LEVEL1_NAME,
118 MAX(LEVEL2_NAME) LEVEL2_NAME,
119 MAX(LEVEL3_NAME) LEVEL3_NAME,
120 MAX(LEVEL4_NAME) LEVEL4_NAME,
121 MAX(LEVEL5_NAME) LEVEL5_NAME,
122 MAX(LEVEL6_NAME) LEVEL6_NAME,
123 MAX(LEVEL7_NAME) LEVEL7_NAME,
124 MAX(LEVEL8_NAME) LEVEL8_NAME,
125 MAX(LEVEL9_NAME) LEVEL9_NAME
126 FROM (SELECT HIERARCHY_ID,
127 B.ITEM_PK_KEY,
128 (CASE
129 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
130 ELSE NULL
131 END) LEVEL9_FK_KEY,
132 (CASE
133 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
134 ELSE NULL
135 END) LEVEL8_FK_KEY,
136 (CASE
137 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
138 ELSE NULL
139 END) LEVEL7_FK_KEY,
140 (CASE
141 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
142 ELSE NULL
143 END) LEVEL6_FK_KEY,
144 (CASE
145 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
146 ELSE NULL
147 END) LEVEL5_FK_KEY,
148 (CASE
149 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
150 ELSE NULL
151 END) LEVEL4_FK_KEY,
152 (CASE
153 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
154 ELSE NULL
155 END) LEVEL3_FK_KEY,
156 (CASE
157 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
158 ELSE NULL
159 END) LEVEL2_FK_KEY,
160 (CASE
161 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
162 ELSE NULL
163 END) LEVEL1_FK_KEY,
164 10 LEVEL_NUM,
165 B.ITEM_NAME,
166 (CASE
167 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
168 ELSE NULL
169 END) LEVEL9_NAME,
170 (CASE
171 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
172 ELSE NULL
173 END) LEVEL8_NAME,
174 (CASE
175 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
176 ELSE NULL
177 END) LEVEL7_NAME,
178 (CASE
179 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
180 ELSE NULL
181 END) LEVEL6_NAME,
182 (CASE
183 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
184 ELSE NULL
185 END) LEVEL5_NAME,
186 (CASE
187 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
188 ELSE NULL
189 END) LEVEL4_NAME,
190 (CASE
191 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
192 ELSE NULL
193 END) LEVEL3_NAME,
194 (CASE
195 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
196 ELSE NULL
197 END) LEVEL2_NAME,
198 (CASE
199 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
200 ELSE NULL
201 END) LEVEL1_NAME
202 FROM (SELECT LEVEL LEVEL_NUM,
203 ITEM_PK_KEY,
204 LEVEL_FK_KEY,
205 LEVEL_NAME,
206 PARENT_FK_KEY,
207 PARENT_NAME,
208 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
209 HIERARCHY_ID
210 FROM MTH_ITEM_HIERARCHY,
211 MTH_ITEMS_D B
212 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
213 START WITH B.ITEM_PK_KEY IS NOT NULL
214 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
215 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
216 (SELECT ITEM_PK_KEY, ITEM_NAME
217 FROM MTH_ITEMS_D) B
218 WHERE A.PATH LIKE '/'
219 ||B.ITEM_PK_KEY
220 ||'%')
221 --The granuality level is item and this would be done
222 -- for all the hierarchies
223 GROUP BY HIERARCHY_ID,ITEM_PK_KEY
224 UNION
225 select mdh.hierarchy_id,
226 MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
227 ,v_unassigned_key level1_level_key
228 ,v_unassigned_key level2_level_key
229 ,v_unassigned_key level3_level_key
230 ,v_unassigned_key level4_level_key
231 ,v_unassigned_key level5_level_key
232 ,v_unassigned_key level6_level_key
233 ,v_unassigned_key level7_level_key
234 ,v_unassigned_key level8_level_key
235 ,v_unassigned_key level9_level_key
236 ,10 level_num
237 ,v_unassigned_item_name item_name
238 ,v_unassigned_category_name level1_name
239 ,v_unassigned_category_name level2_name
240 ,v_unassigned_category_name level3_name
241 ,v_unassigned_category_name level4_name
242 ,v_unassigned_category_name level5_name
243 ,v_unassigned_category_name level6_name
244 ,v_unassigned_category_name level7_name
245 ,v_unassigned_category_name level8_name
246 ,v_unassigned_category_name level9_name
247 from dual,
248 mth_dim_hierarchy mdh,
249 (select distinct hierarchy_id
250 from mth_dim_level_lookup) mdll
251 where mdh.dimension_name= 'ITEM' and
252 mdll.hierarchy_id (+) = mdh.hierarchy_id);
253
254 -- Balance the item denorm table
255 mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');
256
257 -- Push up and fill the level key and name for the ones with NULL
258
259 UPDATE MTH_ITEM_DENORM_D
260 SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
261 level1_name = nvl(level1_name, v_unassigned_category_name),
262 level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
263 level2_name = nvl(level2_name, v_unassigned_category_name),
264 level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
265 level3_name = nvl(level3_name, v_unassigned_category_name),
266 level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
267 level4_name = nvl(level4_name, v_unassigned_category_name),
268 level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
269 level5_name = nvl(level5_name, v_unassigned_category_name),
270 level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
271 level6_name = nvl(level6_name, v_unassigned_category_name),
272 level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
273 level7_name = nvl(level7_name, v_unassigned_category_name),
274 level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
275 level8_name = nvl(level8_name, v_unassigned_category_name),
276 level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
277 level9_name = nvl(level9_name, v_unassigned_category_name);
278
279 -- Add entries for all the rest of levels
280
281
282 Insert into mth_item_denorm_d
283 (
284 HIERARCHY_ID,
285 ITEM_FK_KEY,
286 LEVEL1_FK_KEY,
287 LEVEL2_FK_KEY,
288 LEVEL3_FK_KEY,
289 LEVEL4_FK_KEY,
290 LEVEL5_FK_KEY,
291 LEVEL6_FK_KEY,
292 LEVEL7_FK_KEY,
293 LEVEL8_FK_KEY,
294 LEVEL9_FK_KEY,
295 LEVEL_NUM,
296 ITEM_NAME,
297 LEVEL1_NAME,
298 LEVEL2_NAME,
299 LEVEL3_NAME,
300 LEVEL4_NAME,
301 LEVEL5_NAME,
302 LEVEL6_NAME,
303 LEVEL7_NAME,
304 LEVEL8_NAME,
305 LEVEL9_NAME
306 )
307 select distinct
308 HIERARCHY_ID,
309 NULL,
310 LEVEL1_FK_KEY,
311 LEVEL2_FK_KEY,
312 LEVEL3_FK_KEY,
313 LEVEL4_FK_KEY,
314 LEVEL5_FK_KEY,
315 LEVEL6_FK_KEY,
316 LEVEL7_FK_KEY,
317 LEVEL8_FK_KEY,
318 LEVEL9_FK_KEY,
319 9,
320 NULL,
321 LEVEL1_NAME,
322 LEVEL2_NAME,
323 LEVEL3_NAME,
324 LEVEL4_NAME,
325 LEVEL5_NAME,
326 LEVEL6_NAME,
327 LEVEL7_NAME,
328 LEVEL8_NAME,
329 LEVEL9_NAME
330 From mth_item_denorm_d
331 Where LEVEL9_FK_KEY is not null and level_NUM = 10;
332
333 -- insert level 8 entries
334 Insert into mth_item_denorm_d
335 (
336 HIERARCHY_ID,
337 ITEM_FK_KEY,
338 LEVEL1_FK_KEY,
339 LEVEL2_FK_KEY,
340 LEVEL3_FK_KEY,
341 LEVEL4_FK_KEY,
342 LEVEL5_FK_KEY,
343 LEVEL6_FK_KEY,
344 LEVEL7_FK_KEY,
345 LEVEL8_FK_KEY,
346 LEVEL9_FK_KEY,
347 LEVEL_NUM,
348 ITEM_NAME,
349 LEVEL1_NAME,
350 LEVEL2_NAME,
351 LEVEL3_NAME,
352 LEVEL4_NAME,
356 LEVEL8_NAME,
353 LEVEL5_NAME,
354 LEVEL6_NAME,
355 LEVEL7_NAME,
357 LEVEL9_NAME
358 )
359 select distinct
360 HIERARCHY_ID,
361 NULL,
362 LEVEL1_FK_KEY,
363 LEVEL2_FK_KEY,
364 LEVEL3_FK_KEY,
365 LEVEL4_FK_KEY,
366 LEVEL5_FK_KEY,
367 LEVEL6_FK_KEY,
368 LEVEL7_FK_KEY,
369 LEVEL8_FK_KEY,
370 NULL,
371 8,
372 NULL,
373 LEVEL1_NAME,
374 LEVEL2_NAME,
375 LEVEL3_NAME,
376 LEVEL4_NAME,
377 LEVEL5_NAME,
378 LEVEL6_NAME,
379 LEVEL7_NAME,
380 LEVEL8_NAME,
381 NULL
382 From mth_item_denorm_d
383 Where LEVEL8_FK_KEY is not null and level_NUM = 9;
384
385 -- insert level 7 entries
386 Insert into mth_item_denorm_d
387 (
388 HIERARCHY_ID,
389 ITEM_FK_KEY,
390 LEVEL1_FK_KEY,
391 LEVEL2_FK_KEY,
392 LEVEL3_FK_KEY,
393 LEVEL4_FK_KEY,
394 LEVEL5_FK_KEY,
395 LEVEL6_FK_KEY,
396 LEVEL7_FK_KEY,
397 LEVEL8_FK_KEY,
398 LEVEL9_FK_KEY,
399 LEVEL_NUM,
400 ITEM_NAME,
401 LEVEL1_NAME,
402 LEVEL2_NAME,
403 LEVEL3_NAME,
404 LEVEL4_NAME,
405 LEVEL5_NAME,
406 LEVEL6_NAME,
407 LEVEL7_NAME,
408 LEVEL8_NAME,
409 LEVEL9_NAME
410 )
411 select distinct
412 HIERARCHY_ID,
413 NULL,
414 LEVEL1_FK_KEY,
415 LEVEL2_FK_KEY,
416 LEVEL3_FK_KEY,
417 LEVEL4_FK_KEY,
418 LEVEL5_FK_KEY,
419 LEVEL6_FK_KEY,
420 LEVEL7_FK_KEY,
421 NULL,
422 NULL,
423 7,
424 NULL,
425 LEVEL1_NAME,
426 LEVEL2_NAME,
427 LEVEL3_NAME,
428 LEVEL4_NAME,
429 LEVEL5_NAME,
430 LEVEL6_NAME,
431 LEVEL7_NAME,
432 NULL,
433 NULL
434 From mth_item_denorm_d
435 Where LEVEL7_FK_KEY is not null and level_NUM = 8;
436
437 -- insert level 6 entries
438 Insert into mth_item_denorm_d
439 (
440 HIERARCHY_ID,
441 ITEM_FK_KEY,
442 LEVEL1_FK_KEY,
443 LEVEL2_FK_KEY,
444 LEVEL3_FK_KEY,
445 LEVEL4_FK_KEY,
446 LEVEL5_FK_KEY,
447 LEVEL6_FK_KEY,
448 LEVEL7_FK_KEY,
449 LEVEL8_FK_KEY,
450 LEVEL9_FK_KEY,
451 LEVEL_NUM,
452 ITEM_NAME,
453 LEVEL1_NAME,
454 LEVEL2_NAME,
455 LEVEL3_NAME,
456 LEVEL4_NAME,
457 LEVEL5_NAME,
458 LEVEL6_NAME,
459 LEVEL7_NAME,
460 LEVEL8_NAME,
461 LEVEL9_NAME
462 )
463 select distinct
464 HIERARCHY_ID,
465 NULL,
466 LEVEL1_FK_KEY,
467 LEVEL2_FK_KEY,
468 LEVEL3_FK_KEY,
469 LEVEL4_FK_KEY,
470 LEVEL5_FK_KEY,
471 LEVEL6_FK_KEY,
472 NULL,
473 NULL,
474 NULL,
475 6,
476 NULL,
477 LEVEL1_NAME,
478 LEVEL2_NAME,
479 LEVEL3_NAME,
480 LEVEL4_NAME,
481 LEVEL5_NAME,
482 LEVEL6_NAME,
483 NULL,
484 NULL,
485 NULL
486 From mth_item_denorm_d
487 Where LEVEL6_FK_KEY is not null and level_NUM = 7;
488
489 -- insert level 5 entries
490 Insert into mth_item_denorm_d
491 (
492 HIERARCHY_ID,
493 ITEM_FK_KEY,
494 LEVEL1_FK_KEY,
495 LEVEL2_FK_KEY,
496 LEVEL3_FK_KEY,
497 LEVEL4_FK_KEY,
498 LEVEL5_FK_KEY,
499 LEVEL6_FK_KEY,
500 LEVEL7_FK_KEY,
501 LEVEL8_FK_KEY,
502 LEVEL9_FK_KEY,
503 LEVEL_NUM,
504 ITEM_NAME,
505 LEVEL1_NAME,
506 LEVEL2_NAME,
507 LEVEL3_NAME,
508 LEVEL4_NAME,
509 LEVEL5_NAME,
510 LEVEL6_NAME,
511 LEVEL7_NAME,
512 LEVEL8_NAME,
513 LEVEL9_NAME
514 )
515 select distinct
516 HIERARCHY_ID,
517 NULL,
518 LEVEL1_FK_KEY,
519 LEVEL2_FK_KEY,
520 LEVEL3_FK_KEY,
521 LEVEL4_FK_KEY,
522 LEVEL5_FK_KEY,
523 NULL,
524 NULL,
525 NULL,
526 NULL,
527 5,
528 NULL,
529 LEVEL1_NAME,
530 LEVEL2_NAME,
531 LEVEL3_NAME,
532 LEVEL4_NAME,
533 LEVEL5_NAME,
534 NULL,
535 NULL,
536 NULL,
537 NULL
538 From mth_item_denorm_d
539 Where LEVEL5_FK_KEY is not null and level_NUM = 6;
540
541 -- insert level 4 entries
542 Insert into mth_item_denorm_d
543 (
544 HIERARCHY_ID,
545 ITEM_FK_KEY,
546 LEVEL1_FK_KEY,
547 LEVEL2_FK_KEY,
548 LEVEL3_FK_KEY,
549 LEVEL4_FK_KEY,
553 LEVEL8_FK_KEY,
550 LEVEL5_FK_KEY,
551 LEVEL6_FK_KEY,
552 LEVEL7_FK_KEY,
554 LEVEL9_FK_KEY,
555 LEVEL_NUM,
556 ITEM_NAME,
557 LEVEL1_NAME,
558 LEVEL2_NAME,
559 LEVEL3_NAME,
560 LEVEL4_NAME,
561 LEVEL5_NAME,
562 LEVEL6_NAME,
563 LEVEL7_NAME,
564 LEVEL8_NAME,
565 LEVEL9_NAME
566 )
567 select distinct
568 HIERARCHY_ID,
569 NULL,
570 LEVEL1_FK_KEY,
571 LEVEL2_FK_KEY,
572 LEVEL3_FK_KEY,
573 LEVEL4_FK_KEY,
574 NULL,
575 NULL,
576 NULL,
577 NULL,
578 NULL,
579 4,
580 NULL,
581 LEVEL1_NAME,
582 LEVEL2_NAME,
583 LEVEL3_NAME,
584 LEVEL4_NAME,
585 NULL,
586 NULL,
587 NULL,
588 NULL,
589 NULL
590 From mth_item_denorm_d
591 Where LEVEL4_FK_KEY is not null and level_NUM = 5;
592
593
594 -- insert level 3 entries
595 Insert into mth_item_denorm_d
596 (
597 HIERARCHY_ID,
598 ITEM_FK_KEY,
599 LEVEL1_FK_KEY,
600 LEVEL2_FK_KEY,
601 LEVEL3_FK_KEY,
602 LEVEL4_FK_KEY,
603 LEVEL5_FK_KEY,
604 LEVEL6_FK_KEY,
605 LEVEL7_FK_KEY,
606 LEVEL8_FK_KEY,
607 LEVEL9_FK_KEY,
608 LEVEL_NUM,
609 ITEM_NAME,
610 LEVEL1_NAME,
611 LEVEL2_NAME,
612 LEVEL3_NAME,
613 LEVEL4_NAME,
614 LEVEL5_NAME,
615 LEVEL6_NAME,
616 LEVEL7_NAME,
617 LEVEL8_NAME,
618 LEVEL9_NAME
619 )
620 select distinct
621 HIERARCHY_ID,
622 NULL,
623 LEVEL1_FK_KEY,
624 LEVEL2_FK_KEY,
625 LEVEL3_FK_KEY,
626 NULL,
627 NULL,
628 NULL,
629 NULL,
630 NULL,
631 NULL,
632 3,
633 NULL,
634 LEVEL1_NAME,
635 LEVEL2_NAME,
636 LEVEL3_NAME,
637 NULL,
638 NULL,
639 NULL,
640 NULL,
641 NULL,
642 NULL
643 From mth_item_denorm_d
644 Where LEVEL3_FK_KEY is not null and level_NUM = 4;
645
646
647 -- insert level 2 entries
648 Insert into mth_item_denorm_d
649 (
650 HIERARCHY_ID,
651 ITEM_FK_KEY,
652 LEVEL1_FK_KEY,
653 LEVEL2_FK_KEY,
654 LEVEL3_FK_KEY,
655 LEVEL4_FK_KEY,
656 LEVEL5_FK_KEY,
657 LEVEL6_FK_KEY,
658 LEVEL7_FK_KEY,
659 LEVEL8_FK_KEY,
660 LEVEL9_FK_KEY,
661 LEVEL_NUM,
662 ITEM_NAME,
663 LEVEL1_NAME,
664 LEVEL2_NAME,
665 LEVEL3_NAME,
666 LEVEL4_NAME,
667 LEVEL5_NAME,
668 LEVEL6_NAME,
669 LEVEL7_NAME,
670 LEVEL8_NAME,
671 LEVEL9_NAME
672 )
673 select distinct
674 HIERARCHY_ID,
675 NULL,
676 LEVEL1_FK_KEY,
677 LEVEL2_FK_KEY,
678 NULL,
679 NULL,
680 NULL,
681 NULL,
682 NULL,
683 NULL,
684 NULL,
685 2,
686 NULL,
687 LEVEL1_NAME,
688 LEVEL2_NAME,
689 NULL,
690 NULL,
691 NULL,
692 NULL,
693 NULL,
694 NULL,
695 NULL
696 From mth_item_denorm_d
697 Where LEVEL2_FK_KEY is not null and level_NUM = 3;
698
699
700
701 -- insert level 1 entries
702 Insert into mth_item_denorm_d
703 (
704 HIERARCHY_ID,
705 ITEM_FK_KEY,
706 LEVEL1_FK_KEY,
707 LEVEL2_FK_KEY,
708 LEVEL3_FK_KEY,
709 LEVEL4_FK_KEY,
710 LEVEL5_FK_KEY,
711 LEVEL6_FK_KEY,
712 LEVEL7_FK_KEY,
713 LEVEL8_FK_KEY,
714 LEVEL9_FK_KEY,
715 LEVEL_NUM,
716 ITEM_NAME,
717 LEVEL1_NAME,
718 LEVEL2_NAME,
719 LEVEL3_NAME,
720 LEVEL4_NAME,
721 LEVEL5_NAME,
722 LEVEL6_NAME,
723 LEVEL7_NAME,
724 LEVEL8_NAME,
725 LEVEL9_NAME
726 )
727 select distinct
728 HIERARCHY_ID,
729 null,
730 LEVEL1_FK_KEY,
731 NULL,
732 NULL,
733 NULL,
734 NULL,
735 NULL,
736 NULL,
737 NULL,
738 NULL,
739 1,
740 null,
741 LEVEL1_NAME,
742 NULL,
743 NULL,
744 NULL,
745 NULL,
746 NULL,
747 NULL,
748 NULL,
749 NULL
750 From mth_item_denorm_d
751 Where LEVEL1_FK_KEY is not null and level_NUM = 2;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 ROLLBACK;
756 --End of the procedure ITEM_DIM_LOAD_DENORM
757 END ITEM_DIM_LOAD_DENORM;
758
759
760 /* *****************************************************************************
761 * Procedure :MTH_HRCHY_BALANCE_LOAD *
762 * Description :This procedure is used for the balancing of the *
763 * hierarchy. The algorithm used for the balancing is down balancing *
764 * Please refer to the Item fdd for more details on this. *
765 * File Name :MTHUTILS.PLS *
766 * Visibility :Public *
767 * Parameters :fact table name *
768 * Modification log : *
769 * Author Date Change *
770 * Ankit Goyal 17-Aug-2007 Initial Creation *
771 ****************************************************************************** */
772 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2) is
773
774 v_fact_table VARCHAR2(120);
775
776 --user defined type for array of records
777 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
778 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
779
780 --user defined type of record of arrays
781 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
782 hierarchy_id denorm_rec_tab_type,
783 baselevel_fk_key denorm_rec_tab_type,
784 level7_fk_key denorm_rec_tab_type,
785 level6_fk_key denorm_rec_tab_type,
786 level5_fk_key denorm_rec_tab_type,
787 level4_fk_key denorm_rec_tab_type,
788 level3_fk_key denorm_rec_tab_type,
789 level2_fk_key denorm_rec_tab_type,
790 level1_fk_key denorm_rec_tab_type,
791 level9_name denorm_rec_name_tab_type,
792 level7_name denorm_rec_name_tab_type,
793 level6_name denorm_rec_name_tab_type,
794 level5_name denorm_rec_name_tab_type,
795 level4_name denorm_rec_name_tab_type,
796 level3_name denorm_rec_name_tab_type,
797 level2_name denorm_rec_name_tab_type,
798 level1_name denorm_rec_name_tab_type
799 );
800
801 --instantiation of the user defined type
802 --this will be the placeholder for the records fetched from the denorm table
803 denorm_rec denorm_rec_type;
804
805 --user defined cursor to hold the bulk collection of records
806 item_cur SYS_REFCURSOR;
807
808 --variable for the limit of the bulk collection
809 v_limit NUMBER :=5000;
810
811
812 BEGIN
813
814 --initialize the collection
815 denorm_rec := NULL;
816
817 --initialize the fact table name
818 v_fact_table :=p_fact_table;
819
820 --open the cursor
821 OPEN item_cur FOR 'SELECT --select for the newe levels
822 level9_fk_key,hierarchy_id,item_fk_key,
823 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
824 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
825 level6_fk_key_new,
826 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
827 level9_fk_key) level5_fk_key_new,
828 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
829 level8_fk_key,level9_fk_key) level4_fk_key_new,
833 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
830 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
831 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
832 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
834 level2_fk_key_new,
835 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
836 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
837 level9_fk_key) level1_fk_key_new,
838 level9_name,
839 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
840 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
841 level6_name_new,
842 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
843 level9_name) level5_name_new,
844 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
845 level8_name,level9_name) level4_name_new,
846 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
847 level7_name,5,level8_name,level9_name) level3_name_new,
848 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
849 level6_name,5,level7_name,6,level8_name,level9_name)
850 level2_name_new,
851 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
852 level5_name,5,level6_name,6,level7_name,7,level8_name,
853 level9_name) level1_name_new
854 from
855 (--select the levels to be balanced
856 SELECT hierarchy_id ,item_fk_key,
857 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
858 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
859 level1_fk_key,
860 level9_name,level8_name,level7_name,level6_name,
861 level5_name,level4_name,level3_name,level2_name,
862 level1_name,
863 max_level-c_level diff_level
864 FROM
865 (
866 SELECT hierarchy_id ,item_fk_key,
867 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
868 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
869 level1_fk_key,
870 level9_name,level8_name,level7_name,level6_name,
871 level5_name,level4_name,level3_name,level2_name,
872 level1_name,
873 decode(level9_fk_key,NULL,0,1) +
874 decode(level8_fk_key,NULL,0,1) +
875 decode(level7_fk_key,NULL,0,1) +
876 decode(level6_fk_key,NULL,0,1) +
877 decode(level5_fk_key,NULL,0,1) +
878 decode(level4_fk_key,NULL,0,1) +
879 decode(level3_fk_key,NULL,0,1) +
880 decode(level2_fk_key,NULL,0,1) +
881 decode(level1_fk_key,NULL,0,1) c_level,--current level
882 Max(decode(level9_fk_key,NULL,0,1) +
883 decode(level8_fk_key,NULL,0,1) +
884 decode(level7_fk_key,NULL,0,1) +
885 decode(level6_fk_key,NULL,0,1) +
886 decode(level5_fk_key,NULL,0,1) +
887 decode(level4_fk_key,NULL,0,1) +
888 decode(level3_fk_key,NULL,0,1) +
889 decode(level2_fk_key,NULL,0,1) +
890 decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
891 max_level--maximum level in the hierarchy
892 FROM MTH.MTH_ITEM_DENORM_D_TMP
893 WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
894 )
895 WHERE c_level<max_level
896 AND level9_fk_key IS NOT NULL
897 )';
898 LOOP
899 --fetch the rows in in cursor. Bulk collect
900 FETCH item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
901 denorm_rec.hierarchy_id,
902 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
903 denorm_rec.level6_fk_key,
904 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
905 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
906 denorm_rec.level1_fk_key,
907 denorm_rec.level9_name,
908 denorm_rec.level7_name,
909 denorm_rec.level6_name,
910 denorm_rec.level5_name,
911 denorm_rec.level4_name,
912 denorm_rec.level3_name,
913 denorm_rec.level2_name,
914 denorm_rec.level1_name
915 LIMIT v_limit;
916
917 --terminating condition
918 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
919
920 --bulk update using forall
921 FORALL i IN
922 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
923 UPDATE MTH.MTH_ITEM_DENORM_D_TMP
924 SET
925 level8_fk_key = denorm_rec.level9_fk_key(i),
926 level7_fk_key = denorm_rec.level7_fk_key(i),
927 level6_fk_key = denorm_rec.level6_fk_key(i),
928 level5_fk_key = denorm_rec.level5_fk_key(i),
929 level4_fk_key = denorm_rec.level4_fk_key(i),
930 level3_fk_key = denorm_rec.level3_fk_key(i),
931 level2_fk_key = denorm_rec.level2_fk_key(i),
932 level1_fk_key = denorm_rec.level1_fk_key(i),
933 level8_name = denorm_rec.level9_name(i),
934 level7_name = denorm_rec.level7_name(i),
935 level6_name = denorm_rec.level6_name(i),
936 level5_name = denorm_rec.level5_name(i),
937 level4_name = denorm_rec.level4_name(i),
938 level3_name = denorm_rec.level3_name(i),
939 level2_name = denorm_rec.level2_name(i),
940 level1_name = denorm_rec.level1_name(i)
941 WHERE
942 item_fk_key = denorm_rec.baselevel_fk_key(i)
943 AND hierarchy_id= denorm_rec.hierarchy_id(i);
944 END LOOP;
945 --close the cursor
949 EXCEPTION
946 CLOSE item_cur;
947
948 --handle exceptions
950 WHEN NO_DATA_FOUND THEN
951 RAISE_APPLICATION_ERROR (-20001,
952 'Exception has occured');
953
954 END mth_hrchy_balance_load ;
955
956 /* ****************************************************************************
957 * Procedure :ITEM_DIM_LOAD_DENORM_TMP *
958 * Description :This procedure is used to populate global temporary *
959 * table for incremental load. *
960 * File Name :MTHITEMDB.PLS *
961 * Visibility :Private *
962 * Parameters : *
963 * Modification log : *
964 * Author Date Change *
965 * Yong Feng 10-July-2008 Initial Creation *
966 **************************************************************************** */
967
968
969 PROCEDURE ITEM_DIM_LOAD_DENORM_TMP
970 IS
971 v_unassigned_key number;
972 v_unassigned_item_name varchar2(240);
973 v_unassigned_category_name varchar2(240);
974 BEGIN
975
976 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
977
978 BEGIN
979 SELECT ITEM_NAME into v_unassigned_item_name
980 from MTH_ITEMS_D
981 where item_PK_KEY = v_unassigned_key;
982 exception
983 when TOO_MANY_ROWS then
984 v_unassigned_item_name := null;
985 when NO_DATA_FOUND then
986 v_unassigned_item_name := null;
987 when others then
988 v_unassigned_item_name := null;
989 end;
990
991 BEGIN
992 select CATEGORY_NAME into v_unassigned_category_name
993 from MTH_ITEM_CATEGORIES_D
994 where CATEGORY_PK_KEY = v_unassigned_key;
995 exception
996 when TOO_MANY_ROWS then
997 v_unassigned_item_name := null;
998 when NO_DATA_FOUND then
999 v_unassigned_item_name := null;
1000 when others then
1001 v_unassigned_item_name := null;
1002 end;
1003
1004 INSERT
1005 INTO MTH.MTH_ITEM_DENORM_D_TMP
1006 (
1007 HIERARCHY_ID,
1008 ITEM_FK_KEY,
1009 LEVEL1_FK_KEY,
1010 LEVEL2_FK_KEY,
1011 LEVEL3_FK_KEY,
1012 LEVEL4_FK_KEY,
1013 LEVEL5_FK_KEY,
1014 LEVEL6_FK_KEY,
1015 LEVEL7_FK_KEY,
1016 LEVEL8_FK_KEY,
1017 LEVEL9_FK_KEY,
1018 LEVEL_NUM,
1019 ITEM_NAME,
1020 LEVEL1_NAME,
1021 LEVEL2_NAME,
1022 LEVEL3_NAME,
1023 LEVEL4_NAME,
1024 LEVEL5_NAME,
1025 LEVEL6_NAME,
1026 LEVEL7_NAME,
1027 LEVEL8_NAME,
1028 LEVEL9_NAME
1029 )
1030 SELECT HIERARCHY_ID,
1031 ITEM_FK_KEY,
1032 LEVEL1_FK_KEY,
1033 LEVEL2_FK_KEY,
1034 LEVEL3_FK_KEY,
1035 LEVEL4_FK_KEY,
1036 LEVEL5_FK_KEY,
1037 LEVEL6_FK_KEY,
1038 LEVEL7_FK_KEY,
1039 LEVEL8_FK_KEY,
1040 LEVEL9_FK_KEY,
1041 LEVEL_NUM,
1042 ITEM_NAME,
1043 LEVEL1_NAME,
1044 LEVEL2_NAME,
1045 LEVEL3_NAME,
1046 LEVEL4_NAME,
1047 LEVEL5_NAME,
1048 LEVEL6_NAME,
1049 LEVEL7_NAME,
1050 LEVEL8_NAME,
1051 LEVEL9_NAME
1052 FROM (SELECT HIERARCHY_ID,
1053 ITEM_PK_KEY ITEM_FK_KEY,
1054 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
1055 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
1056 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
1057 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
1058 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
1059 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
1060 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
1061 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
1062 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
1063 MAX(LEVEL_NUM) LEVEL_NUM,
1064 MAX(ITEM_NAME) ITEM_NAME,
1065 MAX(LEVEL1_NAME) LEVEL1_NAME,
1066 MAX(LEVEL2_NAME) LEVEL2_NAME,
1067 MAX(LEVEL3_NAME) LEVEL3_NAME,
1068 MAX(LEVEL4_NAME) LEVEL4_NAME,
1069 MAX(LEVEL5_NAME) LEVEL5_NAME,
1070 MAX(LEVEL6_NAME) LEVEL6_NAME,
1071 MAX(LEVEL7_NAME) LEVEL7_NAME,
1072 MAX(LEVEL8_NAME) LEVEL8_NAME,
1073 MAX(LEVEL9_NAME) LEVEL9_NAME
1074 FROM (SELECT HIERARCHY_ID,
1075 B.ITEM_PK_KEY,
1076 (CASE
1077 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
1078 ELSE NULL
1079 END) LEVEL9_FK_KEY,
1080 (CASE
1081 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
1082 ELSE NULL
1083 END) LEVEL8_FK_KEY,
1084 (CASE
1085 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
1086 ELSE NULL
1087 END) LEVEL7_FK_KEY,
1088 (CASE
1089 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
1090 ELSE NULL
1091 END) LEVEL6_FK_KEY,
1092 (CASE
1096 (CASE
1093 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
1094 ELSE NULL
1095 END) LEVEL5_FK_KEY,
1097 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
1098 ELSE NULL
1099 END) LEVEL4_FK_KEY,
1100 (CASE
1101 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
1102 ELSE NULL
1103 END) LEVEL3_FK_KEY,
1104 (CASE
1105 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
1106 ELSE NULL
1107 END) LEVEL2_FK_KEY,
1108 (CASE
1109 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
1110 ELSE NULL
1111 END) LEVEL1_FK_KEY,
1112 10 LEVEL_NUM,
1113 B.ITEM_NAME,
1114 (CASE
1115 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
1116 ELSE NULL
1117 END) LEVEL9_NAME,
1118 (CASE
1119 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
1120 ELSE NULL
1121 END) LEVEL8_NAME,
1122 (CASE
1123 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
1124 ELSE NULL
1125 END) LEVEL7_NAME,
1126 (CASE
1127 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
1128 ELSE NULL
1129 END) LEVEL6_NAME,
1130 (CASE
1131 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
1132 ELSE NULL
1133 END) LEVEL5_NAME,
1134 (CASE
1135 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
1136 ELSE NULL
1137 END) LEVEL4_NAME,
1138 (CASE
1139 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
1140 ELSE NULL
1141 END) LEVEL3_NAME,
1142 (CASE
1143 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
1144 ELSE NULL
1145 END) LEVEL2_NAME,
1146 (CASE
1147 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
1148 ELSE NULL
1149 END) LEVEL1_NAME
1150 FROM (SELECT LEVEL LEVEL_NUM,
1151 ITEM_PK_KEY,
1152 LEVEL_FK_KEY,
1153 LEVEL_NAME,
1154 PARENT_FK_KEY,
1155 PARENT_NAME,
1156 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
1157 HIERARCHY_ID
1158 FROM MTH_ITEM_HIERARCHY,
1159 MTH_ITEMS_D B
1160 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
1161 START WITH B.ITEM_PK_KEY IS NOT NULL
1162 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
1163 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
1164 (SELECT ITEM_PK_KEY, ITEM_NAME
1165 FROM MTH_ITEMS_D) B
1166 WHERE A.PATH LIKE '/'
1167 ||B.ITEM_PK_KEY
1168 ||'%')
1169 --The granuality level is item and this would be done
1170 -- for all the hierarchies
1171 GROUP BY HIERARCHY_ID,ITEM_PK_KEY
1172 UNION
1173 select mdh.hierarchy_id,
1174 MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
1175 ,v_unassigned_key level1_level_key
1176 ,v_unassigned_key level2_level_key
1177 ,v_unassigned_key level3_level_key
1178 ,v_unassigned_key level4_level_key
1179 ,v_unassigned_key level5_level_key
1180 ,v_unassigned_key level6_level_key
1181 ,v_unassigned_key level7_level_key
1182 ,v_unassigned_key level8_level_key
1183 ,v_unassigned_key level9_level_key
1184 ,10 level_num
1185 ,v_unassigned_item_name item_name
1186 ,v_unassigned_category_name level1_name
1187 ,v_unassigned_category_name level2_name
1188 ,v_unassigned_category_name level3_name
1189 ,v_unassigned_category_name level4_name
1190 ,v_unassigned_category_name level5_name
1191 ,v_unassigned_category_name level6_name
1192 ,v_unassigned_category_name level7_name
1193 ,v_unassigned_category_name level8_name
1194 ,v_unassigned_category_name level9_name
1195 from dual,
1196 mth_dim_hierarchy mdh,
1197 (select distinct hierarchy_id
1198 from mth_dim_level_lookup) mdll
1199 where mdh.dimension_name= 'ITEM' and
1200 mdll.hierarchy_id (+) = mdh.hierarchy_id);
1201
1202 -- Balance the item denorm table
1203 mth_hrchy_balance_load('MTH.MTH_ITEM_DENORM_D_TMP');
1204 -- Push up and fill the level key and name for the ones with NULL
1205
1206 UPDATE MTH.MTH_ITEM_DENORM_D_TMP
1207 SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
1208 level1_name = nvl(level1_name, v_unassigned_category_name),
1209 level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
1210 level2_name = nvl(level2_name, v_unassigned_category_name),
1211 level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
1212 level3_name = nvl(level3_name, v_unassigned_category_name),
1216 level5_name = nvl(level5_name, v_unassigned_category_name),
1213 level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
1214 level4_name = nvl(level4_name, v_unassigned_category_name),
1215 level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
1217 level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
1218 level6_name = nvl(level6_name, v_unassigned_category_name),
1219 level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
1220 level7_name = nvl(level7_name, v_unassigned_category_name),
1221 level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
1222 level8_name = nvl(level8_name, v_unassigned_category_name),
1223 level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
1224 level9_name = nvl(level9_name, v_unassigned_category_name);
1225
1226 -- Add entries for all the rest of levels
1227
1228
1229 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1230 (
1231 HIERARCHY_ID,
1232 ITEM_FK_KEY,
1233 LEVEL1_FK_KEY,
1234 LEVEL2_FK_KEY,
1235 LEVEL3_FK_KEY,
1236 LEVEL4_FK_KEY,
1237 LEVEL5_FK_KEY,
1238 LEVEL6_FK_KEY,
1239 LEVEL7_FK_KEY,
1240 LEVEL8_FK_KEY,
1241 LEVEL9_FK_KEY,
1242 LEVEL_NUM,
1243 ITEM_NAME,
1244 LEVEL1_NAME,
1245 LEVEL2_NAME,
1246 LEVEL3_NAME,
1247 LEVEL4_NAME,
1248 LEVEL5_NAME,
1249 LEVEL6_NAME,
1250 LEVEL7_NAME,
1251 LEVEL8_NAME,
1252 LEVEL9_NAME
1253 )
1254 select distinct
1255 HIERARCHY_ID,
1256 NULL,
1257 LEVEL1_FK_KEY,
1258 LEVEL2_FK_KEY,
1259 LEVEL3_FK_KEY,
1260 LEVEL4_FK_KEY,
1261 LEVEL5_FK_KEY,
1262 LEVEL6_FK_KEY,
1263 LEVEL7_FK_KEY,
1264 LEVEL8_FK_KEY,
1265 LEVEL9_FK_KEY,
1266 9,
1267 NULL,
1268 LEVEL1_NAME,
1269 LEVEL2_NAME,
1270 LEVEL3_NAME,
1271 LEVEL4_NAME,
1272 LEVEL5_NAME,
1273 LEVEL6_NAME,
1274 LEVEL7_NAME,
1275 LEVEL8_NAME,
1276 LEVEL9_NAME
1277 From MTH.MTH_ITEM_DENORM_D_TMP
1278 Where LEVEL9_FK_KEY is not null and level_NUM = 10;
1279
1280 -- insert level 8 entries
1281 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1282 (
1283 HIERARCHY_ID,
1284 ITEM_FK_KEY,
1285 LEVEL1_FK_KEY,
1286 LEVEL2_FK_KEY,
1287 LEVEL3_FK_KEY,
1288 LEVEL4_FK_KEY,
1289 LEVEL5_FK_KEY,
1290 LEVEL6_FK_KEY,
1291 LEVEL7_FK_KEY,
1292 LEVEL8_FK_KEY,
1293 LEVEL9_FK_KEY,
1294 LEVEL_NUM,
1295 ITEM_NAME,
1296 LEVEL1_NAME,
1297 LEVEL2_NAME,
1298 LEVEL3_NAME,
1299 LEVEL4_NAME,
1300 LEVEL5_NAME,
1301 LEVEL6_NAME,
1302 LEVEL7_NAME,
1303 LEVEL8_NAME,
1304 LEVEL9_NAME
1305 )
1306 select distinct
1307 HIERARCHY_ID,
1308 NULL,
1309 LEVEL1_FK_KEY,
1310 LEVEL2_FK_KEY,
1311 LEVEL3_FK_KEY,
1312 LEVEL4_FK_KEY,
1313 LEVEL5_FK_KEY,
1314 LEVEL6_FK_KEY,
1315 LEVEL7_FK_KEY,
1316 LEVEL8_FK_KEY,
1317 NULL,
1318 8,
1319 NULL,
1320 LEVEL1_NAME,
1321 LEVEL2_NAME,
1322 LEVEL3_NAME,
1323 LEVEL4_NAME,
1324 LEVEL5_NAME,
1325 LEVEL6_NAME,
1326 LEVEL7_NAME,
1327 LEVEL8_NAME,
1328 NULL
1329 From MTH.MTH_ITEM_DENORM_D_TMP
1330 Where LEVEL8_FK_KEY is not null and level_NUM = 9;
1331
1332 -- insert level 7 entries
1333 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1334 (
1335 HIERARCHY_ID,
1336 ITEM_FK_KEY,
1337 LEVEL1_FK_KEY,
1338 LEVEL2_FK_KEY,
1339 LEVEL3_FK_KEY,
1340 LEVEL4_FK_KEY,
1341 LEVEL5_FK_KEY,
1342 LEVEL6_FK_KEY,
1343 LEVEL7_FK_KEY,
1344 LEVEL8_FK_KEY,
1345 LEVEL9_FK_KEY,
1346 LEVEL_NUM,
1347 ITEM_NAME,
1348 LEVEL1_NAME,
1349 LEVEL2_NAME,
1350 LEVEL3_NAME,
1351 LEVEL4_NAME,
1352 LEVEL5_NAME,
1353 LEVEL6_NAME,
1354 LEVEL7_NAME,
1355 LEVEL8_NAME,
1356 LEVEL9_NAME
1357 )
1358 select distinct
1359 HIERARCHY_ID,
1360 NULL,
1361 LEVEL1_FK_KEY,
1362 LEVEL2_FK_KEY,
1363 LEVEL3_FK_KEY,
1364 LEVEL4_FK_KEY,
1365 LEVEL5_FK_KEY,
1366 LEVEL6_FK_KEY,
1367 LEVEL7_FK_KEY,
1368 NULL,
1369 NULL,
1370 7,
1371 NULL,
1372 LEVEL1_NAME,
1373 LEVEL2_NAME,
1374 LEVEL3_NAME,
1375 LEVEL4_NAME,
1376 LEVEL5_NAME,
1377 LEVEL6_NAME,
1378 LEVEL7_NAME,
1379 NULL,
1380 NULL
1381 From MTH.MTH_ITEM_DENORM_D_TMP
1382 Where LEVEL7_FK_KEY is not null and level_NUM = 8;
1383
1384 -- insert level 6 entries
1385 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1386 (
1387 HIERARCHY_ID,
1388 ITEM_FK_KEY,
1389 LEVEL1_FK_KEY,
1390 LEVEL2_FK_KEY,
1391 LEVEL3_FK_KEY,
1392 LEVEL4_FK_KEY,
1396 LEVEL8_FK_KEY,
1393 LEVEL5_FK_KEY,
1394 LEVEL6_FK_KEY,
1395 LEVEL7_FK_KEY,
1397 LEVEL9_FK_KEY,
1398 LEVEL_NUM,
1399 ITEM_NAME,
1400 LEVEL1_NAME,
1401 LEVEL2_NAME,
1402 LEVEL3_NAME,
1403 LEVEL4_NAME,
1404 LEVEL5_NAME,
1405 LEVEL6_NAME,
1406 LEVEL7_NAME,
1407 LEVEL8_NAME,
1408 LEVEL9_NAME
1409 )
1410 select distinct
1411 HIERARCHY_ID,
1412 NULL,
1413 LEVEL1_FK_KEY,
1414 LEVEL2_FK_KEY,
1415 LEVEL3_FK_KEY,
1416 LEVEL4_FK_KEY,
1417 LEVEL5_FK_KEY,
1418 LEVEL6_FK_KEY,
1419 NULL,
1420 NULL,
1421 NULL,
1422 6,
1423 NULL,
1424 LEVEL1_NAME,
1425 LEVEL2_NAME,
1426 LEVEL3_NAME,
1427 LEVEL4_NAME,
1428 LEVEL5_NAME,
1429 LEVEL6_NAME,
1430 NULL,
1431 NULL,
1432 NULL
1433 From MTH.MTH_ITEM_DENORM_D_TMP
1434 Where LEVEL6_FK_KEY is not null and level_NUM = 7;
1435
1436 -- insert level 5 entries
1437 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1438 (
1439 HIERARCHY_ID,
1440 ITEM_FK_KEY,
1441 LEVEL1_FK_KEY,
1442 LEVEL2_FK_KEY,
1443 LEVEL3_FK_KEY,
1444 LEVEL4_FK_KEY,
1445 LEVEL5_FK_KEY,
1446 LEVEL6_FK_KEY,
1447 LEVEL7_FK_KEY,
1448 LEVEL8_FK_KEY,
1449 LEVEL9_FK_KEY,
1450 LEVEL_NUM,
1451 ITEM_NAME,
1452 LEVEL1_NAME,
1453 LEVEL2_NAME,
1454 LEVEL3_NAME,
1455 LEVEL4_NAME,
1456 LEVEL5_NAME,
1457 LEVEL6_NAME,
1458 LEVEL7_NAME,
1459 LEVEL8_NAME,
1460 LEVEL9_NAME
1461 )
1462 select distinct
1463 HIERARCHY_ID,
1464 NULL,
1465 LEVEL1_FK_KEY,
1466 LEVEL2_FK_KEY,
1467 LEVEL3_FK_KEY,
1468 LEVEL4_FK_KEY,
1469 LEVEL5_FK_KEY,
1470 NULL,
1471 NULL,
1472 NULL,
1473 NULL,
1474 5,
1475 NULL,
1476 LEVEL1_NAME,
1477 LEVEL2_NAME,
1478 LEVEL3_NAME,
1479 LEVEL4_NAME,
1480 LEVEL5_NAME,
1481 NULL,
1482 NULL,
1483 NULL,
1484 NULL
1485 From MTH.MTH_ITEM_DENORM_D_TMP
1486 Where LEVEL5_FK_KEY is not null and level_NUM = 6;
1487
1488 -- insert level 4 entries
1489 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1490 (
1491 HIERARCHY_ID,
1492 ITEM_FK_KEY,
1493 LEVEL1_FK_KEY,
1494 LEVEL2_FK_KEY,
1495 LEVEL3_FK_KEY,
1496 LEVEL4_FK_KEY,
1497 LEVEL5_FK_KEY,
1498 LEVEL6_FK_KEY,
1499 LEVEL7_FK_KEY,
1500 LEVEL8_FK_KEY,
1501 LEVEL9_FK_KEY,
1502 LEVEL_NUM,
1503 ITEM_NAME,
1504 LEVEL1_NAME,
1505 LEVEL2_NAME,
1506 LEVEL3_NAME,
1507 LEVEL4_NAME,
1508 LEVEL5_NAME,
1509 LEVEL6_NAME,
1510 LEVEL7_NAME,
1511 LEVEL8_NAME,
1512 LEVEL9_NAME
1513 )
1514 select distinct
1515 HIERARCHY_ID,
1516 NULL,
1517 LEVEL1_FK_KEY,
1518 LEVEL2_FK_KEY,
1519 LEVEL3_FK_KEY,
1520 LEVEL4_FK_KEY,
1521 NULL,
1522 NULL,
1523 NULL,
1524 NULL,
1525 NULL,
1526 4,
1527 NULL,
1528 LEVEL1_NAME,
1529 LEVEL2_NAME,
1530 LEVEL3_NAME,
1531 LEVEL4_NAME,
1532 NULL,
1533 NULL,
1534 NULL,
1535 NULL,
1536 NULL
1537 From MTH.MTH_ITEM_DENORM_D_TMP
1538 Where LEVEL4_FK_KEY is not null and level_NUM = 5;
1539
1540
1541 -- insert level 3 entries
1542 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1543 (
1544 HIERARCHY_ID,
1545 ITEM_FK_KEY,
1546 LEVEL1_FK_KEY,
1547 LEVEL2_FK_KEY,
1548 LEVEL3_FK_KEY,
1549 LEVEL4_FK_KEY,
1550 LEVEL5_FK_KEY,
1551 LEVEL6_FK_KEY,
1552 LEVEL7_FK_KEY,
1553 LEVEL8_FK_KEY,
1554 LEVEL9_FK_KEY,
1555 LEVEL_NUM,
1556 ITEM_NAME,
1557 LEVEL1_NAME,
1558 LEVEL2_NAME,
1559 LEVEL3_NAME,
1560 LEVEL4_NAME,
1561 LEVEL5_NAME,
1562 LEVEL6_NAME,
1563 LEVEL7_NAME,
1564 LEVEL8_NAME,
1565 LEVEL9_NAME
1566 )
1567 select distinct
1568 HIERARCHY_ID,
1569 NULL,
1570 LEVEL1_FK_KEY,
1571 LEVEL2_FK_KEY,
1572 LEVEL3_FK_KEY,
1573 NULL,
1574 NULL,
1575 NULL,
1576 NULL,
1577 NULL,
1578 NULL,
1579 3,
1580 NULL,
1581 LEVEL1_NAME,
1582 LEVEL2_NAME,
1583 LEVEL3_NAME,
1584 NULL,
1585 NULL,
1586 NULL,
1587 NULL,
1588 NULL,
1589 NULL
1590 From MTH.MTH_ITEM_DENORM_D_TMP
1591 Where LEVEL3_FK_KEY is not null and level_NUM = 4;
1595 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1592
1593
1594 -- insert level 2 entries
1596 (
1597 HIERARCHY_ID,
1598 ITEM_FK_KEY,
1599 LEVEL1_FK_KEY,
1600 LEVEL2_FK_KEY,
1601 LEVEL3_FK_KEY,
1602 LEVEL4_FK_KEY,
1603 LEVEL5_FK_KEY,
1604 LEVEL6_FK_KEY,
1605 LEVEL7_FK_KEY,
1606 LEVEL8_FK_KEY,
1607 LEVEL9_FK_KEY,
1608 LEVEL_NUM,
1609 ITEM_NAME,
1610 LEVEL1_NAME,
1611 LEVEL2_NAME,
1612 LEVEL3_NAME,
1613 LEVEL4_NAME,
1614 LEVEL5_NAME,
1615 LEVEL6_NAME,
1616 LEVEL7_NAME,
1617 LEVEL8_NAME,
1618 LEVEL9_NAME
1619 )
1620 select distinct
1621 HIERARCHY_ID,
1622 NULL,
1623 LEVEL1_FK_KEY,
1624 LEVEL2_FK_KEY,
1625 NULL,
1626 NULL,
1627 NULL,
1628 NULL,
1629 NULL,
1630 NULL,
1631 NULL,
1632 2,
1633 NULL,
1634 LEVEL1_NAME,
1635 LEVEL2_NAME,
1636 NULL,
1637 NULL,
1638 NULL,
1639 NULL,
1640 NULL,
1641 NULL,
1642 NULL
1643 From MTH.MTH_ITEM_DENORM_D_TMP
1644 Where LEVEL2_FK_KEY is not null and level_NUM = 3;
1645
1646
1647
1648 -- insert level 1 entries
1649 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1650 (
1651 HIERARCHY_ID,
1652 ITEM_FK_KEY,
1653 LEVEL1_FK_KEY,
1654 LEVEL2_FK_KEY,
1655 LEVEL3_FK_KEY,
1656 LEVEL4_FK_KEY,
1657 LEVEL5_FK_KEY,
1658 LEVEL6_FK_KEY,
1659 LEVEL7_FK_KEY,
1660 LEVEL8_FK_KEY,
1661 LEVEL9_FK_KEY,
1662 LEVEL_NUM,
1663 ITEM_NAME,
1664 LEVEL1_NAME,
1665 LEVEL2_NAME,
1666 LEVEL3_NAME,
1667 LEVEL4_NAME,
1668 LEVEL5_NAME,
1669 LEVEL6_NAME,
1670 LEVEL7_NAME,
1671 LEVEL8_NAME,
1672 LEVEL9_NAME
1673 )
1674 select distinct
1675 HIERARCHY_ID,
1676 null,
1677 LEVEL1_FK_KEY,
1678 NULL,
1679 NULL,
1680 NULL,
1681 NULL,
1682 NULL,
1683 NULL,
1684 NULL,
1685 NULL,
1686 1,
1687 null,
1688 LEVEL1_NAME,
1689 NULL,
1690 NULL,
1691 NULL,
1692 NULL,
1693 NULL,
1694 NULL,
1695 NULL,
1696 NULL
1697 From MTH.MTH_ITEM_DENORM_D_TMP
1698 Where LEVEL1_FK_KEY is not null and level_NUM = 2;
1699
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 ROLLBACK;
1704 END ITEM_DIM_LOAD_DENORM_TMP;
1705
1706
1707 PROCEDURE ITEM_DIM_LOAD_DENORM_INCR
1708 IS
1709 BEGIN
1710
1711 ITEM_DIM_LOAD_DENORM_TMP;
1712
1713 -- delete rows that do not exist in the temp table but exist in the denorm table
1714
1715 DELETE FROM mth_item_denorm_d d
1716 WHERE NOT EXISTS
1717 (SELECT 1
1718 FROM mth.mth_item_denorm_d_tmp t
1719 WHERE d.level_num = t.LEVEL_num AND
1720 d.hierarchy_id = t.hierarchy_id AND
1721 (d.item_fk_key = t.item_fk_key OR d.item_fk_key IS null AND t.item_fk_key IS NULL) AND
1722 (d.level9_fk_key = t.level9_fk_key OR d.level9_fk_key IS null AND t.level9_fk_key IS NULL) AND
1723 (d.level8_fk_key = t.level8_fk_key OR d.level8_fk_key IS null AND t.level8_fk_key IS NULL) AND
1724 (d.level7_fk_key = t.level7_fk_key OR d.level7_fk_key IS null AND t.level7_fk_key IS NULL) AND
1725 (d.level6_fk_key = t.level6_fk_key OR d.level6_fk_key IS null AND t.level6_fk_key IS NULL) AND
1726 (d.level5_fk_key = t.level5_fk_key OR d.level5_fk_key IS null AND t.level5_fk_key IS NULL) AND
1727 (d.level4_fk_key = t.level4_fk_key OR d.level4_fk_key IS null AND t.level4_fk_key IS NULL) AND
1728 (d.level3_fk_key = t.level3_fk_key OR d.level3_fk_key IS null AND t.level3_fk_key IS NULL) AND
1729 (d.level2_fk_key = t.level2_fk_key OR d.level2_fk_key IS null AND t.level2_fk_key IS NULL) AND
1730 (d.level1_fk_key = t.level1_fk_key OR d.level1_fk_key IS null AND t.level1_fk_key IS NULL) AND
1731 (d.ITEM_NAME = t.ITEM_NAME OR d.ITEM_NAME IS null AND t.ITEM_NAME IS NULL) AND
1732 (d.LEVEL9_NAME = t.LEVEL9_NAME OR d.LEVEL9_NAME IS null AND t.LEVEL9_NAME IS NULL) AND
1733 (d.LEVEL8_NAME = t.LEVEL8_NAME OR d.LEVEL8_NAME IS null AND t.LEVEL8_NAME IS NULL) AND
1734 (d.LEVEL7_NAME = t.LEVEL7_NAME OR d.LEVEL7_NAME IS null AND t.LEVEL7_NAME IS NULL) AND
1735 (d.LEVEL6_NAME = t.LEVEL6_NAME OR d.LEVEL6_NAME IS null AND t.LEVEL6_NAME IS NULL) AND
1736 (d.LEVEL5_NAME = t.LEVEL5_NAME OR d.LEVEL5_NAME IS null AND t.LEVEL5_NAME IS NULL) AND
1737 (d.LEVEL4_NAME = t.LEVEL4_NAME OR d.LEVEL4_NAME IS null AND t.LEVEL4_NAME IS NULL) AND
1738 (d.LEVEL3_NAME = t.LEVEL3_NAME OR d.LEVEL3_NAME IS null AND t.LEVEL3_NAME IS NULL) AND
1739 (d.LEVEL2_NAME = t.LEVEL2_NAME OR d.LEVEL2_NAME IS null AND t.LEVEL2_NAME IS NULL) AND
1740 (d.LEVEL1_NAME = t.LEVEL1_NAME OR d.LEVEL1_NAME IS null AND t.LEVEL1_NAME IS NULL));
1741
1742
1743 -- insert rows that exists in the temp table but not in the denorm table
1744
1745
1746 insert into mth_item_denorm_d
1747 (
1748 HIERARCHY_ID,
1752 LEVEL2_FK_KEY,
1749 LEVEL_NUM,
1750 ITEM_FK_KEY,
1751 LEVEL1_FK_KEY,
1753 LEVEL3_FK_KEY,
1754 LEVEL4_FK_KEY,
1755 LEVEL5_FK_KEY,
1756 LEVEL6_FK_KEY,
1757 LEVEL7_FK_KEY,
1758 LEVEL8_FK_KEY,
1759 LEVEL9_FK_KEY,
1760 ITEM_NAME,
1761 LEVEL9_NAME,
1762 LEVEL8_NAME,
1763 LEVEL7_NAME,
1764 LEVEL6_NAME,
1765 LEVEL5_NAME,
1766 LEVEL4_NAME,
1767 LEVEL3_NAME,
1768 LEVEL2_NAME,
1769 LEVEL1_NAME
1770 )
1771 SELECT
1772 t.HIERARCHY_ID,
1773 t.LEVEL_NUM,
1774 t.ITEM_FK_KEY,
1775 t.LEVEL1_FK_KEY,
1776 t.LEVEL2_FK_KEY,
1777 t.LEVEL3_FK_KEY,
1778 t.LEVEL4_FK_KEY,
1779 t.LEVEL5_FK_KEY,
1780 t.LEVEL6_FK_KEY,
1781 t.LEVEL7_FK_KEY,
1782 t.LEVEL8_FK_KEY,
1783 t.LEVEL9_FK_KEY,
1784 t.ITEM_NAME,
1785 t.LEVEL9_NAME,
1786 t.LEVEL8_NAME,
1787 t.LEVEL7_NAME,
1788 t.LEVEL6_NAME,
1789 t.LEVEL5_NAME,
1790 t.LEVEL4_NAME,
1791 t.LEVEL3_NAME,
1792 t.LEVEL2_NAME,
1793 t.LEVEL1_NAME
1794 FROM MTH.MTH_ITEM_DENORM_D_TMP t
1795 WHERE NOT EXISTS
1796 (SELECT 1
1797 FROM mth_item_denorm_d d
1798 WHERE d.level_num = t.LEVEL_num AND
1799 d.hierarchy_id = t.hierarchy_id AND
1800 (d.item_fk_key = t.item_fk_key OR d.item_fk_key IS null AND t.item_fk_key IS NULL) AND
1801 (d.level9_fk_key = t.level9_fk_key OR d.level9_fk_key IS null AND t.level9_fk_key IS NULL) AND
1802 (d.level8_fk_key = t.level8_fk_key OR d.level8_fk_key IS null AND t.level8_fk_key IS NULL) AND
1803 (d.level7_fk_key = t.level7_fk_key OR d.level7_fk_key IS null AND t.level7_fk_key IS NULL) AND
1804 (d.level6_fk_key = t.level6_fk_key OR d.level6_fk_key IS null AND t.level6_fk_key IS NULL) AND
1805 (d.level5_fk_key = t.level5_fk_key OR d.level5_fk_key IS null AND t.level5_fk_key IS NULL) AND
1806 (d.level4_fk_key = t.level4_fk_key OR d.level4_fk_key IS null AND t.level4_fk_key IS NULL) AND
1807 (d.level3_fk_key = t.level3_fk_key OR d.level3_fk_key IS null AND t.level3_fk_key IS NULL) AND
1808 (d.level2_fk_key = t.level2_fk_key OR d.level2_fk_key IS null AND t.level2_fk_key IS NULL) AND
1809 (d.level1_fk_key = t.level1_fk_key OR d.level1_fk_key IS null AND t.level1_fk_key IS NULL) AND
1810 (d.ITEM_NAME = t.ITEM_NAME OR d.ITEM_NAME IS null AND t.ITEM_NAME IS NULL) AND
1811 (d.LEVEL9_NAME = t.LEVEL9_NAME OR d.LEVEL9_NAME IS null AND t.LEVEL9_NAME IS NULL) AND
1812 (d.LEVEL8_NAME = t.LEVEL8_NAME OR d.LEVEL8_NAME IS null AND t.LEVEL8_NAME IS NULL) AND
1813 (d.LEVEL7_NAME = t.LEVEL7_NAME OR d.LEVEL7_NAME IS null AND t.LEVEL7_NAME IS NULL) AND
1814 (d.LEVEL6_NAME = t.LEVEL6_NAME OR d.LEVEL6_NAME IS null AND t.LEVEL6_NAME IS NULL) AND
1815 (d.LEVEL5_NAME = t.LEVEL5_NAME OR d.LEVEL5_NAME IS null AND t.LEVEL5_NAME IS NULL) AND
1816 (d.LEVEL4_NAME = t.LEVEL4_NAME OR d.LEVEL4_NAME IS null AND t.LEVEL4_NAME IS NULL) AND
1817 (d.LEVEL3_NAME = t.LEVEL3_NAME OR d.LEVEL3_NAME IS null AND t.LEVEL3_NAME IS NULL) AND
1818 (d.LEVEL2_NAME = t.LEVEL2_NAME OR d.LEVEL2_NAME IS null AND t.LEVEL2_NAME IS NULL) AND
1819 (d.LEVEL1_NAME = t.LEVEL1_NAME OR d.LEVEL1_NAME IS null AND t.LEVEL1_NAME IS NULL));
1820
1821
1822
1823 EXCEPTION
1824 WHEN OTHERS THEN
1825 ROLLBACK;
1826 END ITEM_DIM_LOAD_DENORM_INCR;
1827
1828
1829 /* ****************************************************************************
1830 * Procedure :ITEM_DIM_HRCHY_LEVEL_LOAD *
1831 * Description :This procedure will populate the level information *
1832 * for the item - category and category - category *
1833 * relatiopnships in the item hierarchy staging table *
1834 * File Name :MTHITEMDB.PLS *
1835 * Visibility :Public *
1836 * Parameters : *
1837 * Modification log : *
1838 * Author Date Change *
1839 * Ankit Goyal 29-May--2007 Initial Creation *
1840 **************************************************************************** */
1841
1842 PROCEDURE ITEM_DIM_HRCHY_LEVEL_LOAD
1843 IS
1844 BEGIN
1845
1846 --use connect by to find out the level number for the child and update the row
1847 --the root node will be level 1 and the leaf node will have highest level
1848 --having the hierarchy name in the connect query will allow multiple
1849 --hierarchies to be present in the staging table.
1850 --the start condition of the connect by query is the root node for
1851 --which the parent is null.
1852
1853
1854 UPDATE MTH_ITEM_HIERARCHY_STG A
1855 SET LEVEL_NUM =
1856 (SELECT LEVEL
1857 FROM MTH_ITEM_HIERARCHY_STG B
1858 WHERE A.LEVEL_FK = B.LEVEL_FK
1859 AND A.HIERARCHY_NAME = B.HIERARCHY_NAME CONNECT BY PRIOR
1860 B.LEVEL_FK = B.PARENT_FK
1861 AND PRIOR B.HIERARCHY_NAME = A.HIERARCHY_NAME START
1862 WITH B.PARENT_FK IS NULL
1863 );
1864 COMMIT;
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 ROLLBACK;
1868
1869 --End of the procedure ITEM_DIM_HRCHY_LEVEL_LOAD
1870 END ITEM_DIM_HRCHY_LEVEL_LOAD;
1871
1872
1873 END MTH_ITEM_DIMENSION_PKG;