[Home] [Help]
29: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32: AS num_levels
33: FROM MTH_ITEM_DENORM_D ID
34: WHERE id.LEVEL_num = 10 and
35: ID.hierarchy_id = DH.hierarchy_id )
36: WHERE DH.NUMBER_OF_LEVEL IS NULL AND
37: DH.dimension_name= 'ITEM' AND
34: WHERE id.LEVEL_num = 10 and
35: ID.hierarchy_id = DH.hierarchy_id )
36: WHERE DH.NUMBER_OF_LEVEL IS NULL AND
37: DH.dimension_name= 'ITEM' AND
38: EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
39: WHERE DH.dimension_name= 'ITEM' AND
40: ID.level_num = 10 AND
41: ID.item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
42: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
64: v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
65: v_log_to_date := SYSDATE;
66: /*New additions */
67: -- Call mth_run_log_pre_load
68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);
69: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;
70:
71: --insert the values in the denormalised item denorm table.
72: --use of max function in the select is just to by pass the usage in the
65: v_log_to_date := SYSDATE;
66: /*New additions */
67: -- Call mth_run_log_pre_load
68: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);
69: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;
70:
71: --insert the values in the denormalised item denorm table.
72: --use of max function in the select is just to by pass the usage in the
73: --group by clause.
102: v_unassigned_item_name := null;
103: end;
104:
105: INSERT
106: INTO MTH_ITEM_DENORM_D
107: (
108: HIERARCHY_ID,
109: ITEM_FK_KEY,
110: LEVEL1_FK_KEY,
303: where mdh.dimension_name= 'ITEM' and
304: mdll.hierarchy_id (+) = mdh.hierarchy_id);
305:
306: -- Balance the item denorm table
307: mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');
308:
309: -- Push up and fill the level key and name for the ones with NULL
310:
311: UPDATE MTH_ITEM_DENORM_D
307: mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');
308:
309: -- Push up and fill the level key and name for the ones with NULL
310:
311: UPDATE MTH_ITEM_DENORM_D
312: SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
313: level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
314: level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
315: level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
331:
332: -- Add entries for all the rest of levels
333:
334:
335: Insert into mth_item_denorm_d
336: (
337: HIERARCHY_ID,
338: ITEM_FK_KEY,
339: LEVEL1_FK_KEY,
379: LEVEL6_NAME,
380: LEVEL7_NAME,
381: LEVEL8_NAME,
382: LEVEL9_NAME
383: From mth_item_denorm_d
384: Where LEVEL9_FK_KEY is not null and level_NUM = 10;
385:
386: -- insert level 8 entries
387: Insert into mth_item_denorm_d
383: From mth_item_denorm_d
384: Where LEVEL9_FK_KEY is not null and level_NUM = 10;
385:
386: -- insert level 8 entries
387: Insert into mth_item_denorm_d
388: (
389: HIERARCHY_ID,
390: ITEM_FK_KEY,
391: LEVEL1_FK_KEY,
431: LEVEL6_NAME,
432: LEVEL7_NAME,
433: LEVEL8_NAME,
434: NULL
435: From mth_item_denorm_d
436: Where LEVEL8_FK_KEY is not null and level_NUM = 9;
437:
438: -- insert level 7 entries
439: Insert into mth_item_denorm_d
435: From mth_item_denorm_d
436: Where LEVEL8_FK_KEY is not null and level_NUM = 9;
437:
438: -- insert level 7 entries
439: Insert into mth_item_denorm_d
440: (
441: HIERARCHY_ID,
442: ITEM_FK_KEY,
443: LEVEL1_FK_KEY,
483: LEVEL6_NAME,
484: LEVEL7_NAME,
485: NULL,
486: NULL
487: From mth_item_denorm_d
488: Where LEVEL7_FK_KEY is not null and level_NUM = 8;
489:
490: -- insert level 6 entries
491: Insert into mth_item_denorm_d
487: From mth_item_denorm_d
488: Where LEVEL7_FK_KEY is not null and level_NUM = 8;
489:
490: -- insert level 6 entries
491: Insert into mth_item_denorm_d
492: (
493: HIERARCHY_ID,
494: ITEM_FK_KEY,
495: LEVEL1_FK_KEY,
535: LEVEL6_NAME,
536: NULL,
537: NULL,
538: NULL
539: From mth_item_denorm_d
540: Where LEVEL6_FK_KEY is not null and level_NUM = 7;
541:
542: -- insert level 5 entries
543: Insert into mth_item_denorm_d
539: From mth_item_denorm_d
540: Where LEVEL6_FK_KEY is not null and level_NUM = 7;
541:
542: -- insert level 5 entries
543: Insert into mth_item_denorm_d
544: (
545: HIERARCHY_ID,
546: ITEM_FK_KEY,
547: LEVEL1_FK_KEY,
587: NULL,
588: NULL,
589: NULL,
590: NULL
591: From mth_item_denorm_d
592: Where LEVEL5_FK_KEY is not null and level_NUM = 6;
593:
594: -- insert level 4 entries
595: Insert into mth_item_denorm_d
591: From mth_item_denorm_d
592: Where LEVEL5_FK_KEY is not null and level_NUM = 6;
593:
594: -- insert level 4 entries
595: Insert into mth_item_denorm_d
596: (
597: HIERARCHY_ID,
598: ITEM_FK_KEY,
599: LEVEL1_FK_KEY,
639: NULL,
640: NULL,
641: NULL,
642: NULL
643: From mth_item_denorm_d
644: Where LEVEL4_FK_KEY is not null and level_NUM = 5;
645:
646:
647: -- insert level 3 entries
644: Where LEVEL4_FK_KEY is not null and level_NUM = 5;
645:
646:
647: -- insert level 3 entries
648: Insert into mth_item_denorm_d
649: (
650: HIERARCHY_ID,
651: ITEM_FK_KEY,
652: LEVEL1_FK_KEY,
692: NULL,
693: NULL,
694: NULL,
695: NULL
696: From mth_item_denorm_d
697: Where LEVEL3_FK_KEY is not null and level_NUM = 4;
698:
699:
700: -- insert level 2 entries
697: Where LEVEL3_FK_KEY is not null and level_NUM = 4;
698:
699:
700: -- insert level 2 entries
701: Insert into mth_item_denorm_d
702: (
703: HIERARCHY_ID,
704: ITEM_FK_KEY,
705: LEVEL1_FK_KEY,
745: NULL,
746: NULL,
747: NULL,
748: NULL
749: From mth_item_denorm_d
750: Where LEVEL2_FK_KEY is not null and level_NUM = 3;
751:
752:
753:
751:
752:
753:
754: -- insert level 1 entries
755: Insert into mth_item_denorm_d
756: (
757: HIERARCHY_ID,
758: ITEM_FK_KEY,
759: LEVEL1_FK_KEY,
799: NULL,
800: NULL,
801: NULL,
802: NULL
803: From mth_item_denorm_d
804: Where LEVEL1_FK_KEY is not null and level_NUM = 2;
805:
806:
807: -- UPDATE the number of levels in mth_dim_hierarchy
814:
815:
816: /*New additions */
817: ----Call mth_run_log_post_load
818: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key);
819:
820: EXCEPTION
821: WHEN OTHERS THEN
822: ROLLBACK;
955: decode(level3_fk_key,NULL,0,1) +
956: decode(level2_fk_key,NULL,0,1) +
957: decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
958: max_level--maximum level in the hierarchy
959: FROM MTH_ITEM_DENORM_D_TMP
960: WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
961: )
962: WHERE c_level
986:
987: --bulk update using forall
988: FORALL i IN
989: denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
990: UPDATE MTH_ITEM_DENORM_D_TMP
991: SET
992: level8_fk_key = denorm_rec.level9_fk_key(i),
993: level7_fk_key = denorm_rec.level7_fk_key(i),
994: level6_fk_key = denorm_rec.level6_fk_key(i),
1068: v_unassigned_item_name := null;
1069: end;
1070:
1071: INSERT
1072: INTO MTH.MTH_ITEM_DENORM_D_TMP
1073: (
1074: HIERARCHY_ID,
1075: ITEM_FK_KEY,
1076: LEVEL1_FK_KEY,
1265: where mdh.dimension_name= 'ITEM' and
1266: mdll.hierarchy_id (+) = mdh.hierarchy_id);
1267:
1268: -- Balance the item denorm table
1269: mth_hrchy_balance_load('MTH.MTH_ITEM_DENORM_D_TMP');
1270: -- Push up and fill the level key and name for the ones with NULL
1271:
1272: UPDATE MTH.MTH_ITEM_DENORM_D_TMP
1273: SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
1268: -- Balance the item denorm table
1269: mth_hrchy_balance_load('MTH.MTH_ITEM_DENORM_D_TMP');
1270: -- Push up and fill the level key and name for the ones with NULL
1271:
1272: UPDATE MTH.MTH_ITEM_DENORM_D_TMP
1273: SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
1274: level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
1275: level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
1276: level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
1292:
1293: -- Add entries for all the rest of levels
1294:
1295:
1296: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1297: (
1298: HIERARCHY_ID,
1299: ITEM_FK_KEY,
1300: LEVEL1_FK_KEY,
1340: LEVEL6_NAME,
1341: LEVEL7_NAME,
1342: LEVEL8_NAME,
1343: LEVEL9_NAME
1344: From MTH.MTH_ITEM_DENORM_D_TMP
1345: Where LEVEL9_FK_KEY is not null and level_NUM = 10;
1346:
1347: -- insert level 8 entries
1348: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1344: From MTH.MTH_ITEM_DENORM_D_TMP
1345: Where LEVEL9_FK_KEY is not null and level_NUM = 10;
1346:
1347: -- insert level 8 entries
1348: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1349: (
1350: HIERARCHY_ID,
1351: ITEM_FK_KEY,
1352: LEVEL1_FK_KEY,
1392: LEVEL6_NAME,
1393: LEVEL7_NAME,
1394: LEVEL8_NAME,
1395: NULL
1396: From MTH.MTH_ITEM_DENORM_D_TMP
1397: Where LEVEL8_FK_KEY is not null and level_NUM = 9;
1398:
1399: -- insert level 7 entries
1400: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1396: From MTH.MTH_ITEM_DENORM_D_TMP
1397: Where LEVEL8_FK_KEY is not null and level_NUM = 9;
1398:
1399: -- insert level 7 entries
1400: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1401: (
1402: HIERARCHY_ID,
1403: ITEM_FK_KEY,
1404: LEVEL1_FK_KEY,
1444: LEVEL6_NAME,
1445: LEVEL7_NAME,
1446: NULL,
1447: NULL
1448: From MTH.MTH_ITEM_DENORM_D_TMP
1449: Where LEVEL7_FK_KEY is not null and level_NUM = 8;
1450:
1451: -- insert level 6 entries
1452: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1448: From MTH.MTH_ITEM_DENORM_D_TMP
1449: Where LEVEL7_FK_KEY is not null and level_NUM = 8;
1450:
1451: -- insert level 6 entries
1452: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1453: (
1454: HIERARCHY_ID,
1455: ITEM_FK_KEY,
1456: LEVEL1_FK_KEY,
1496: LEVEL6_NAME,
1497: NULL,
1498: NULL,
1499: NULL
1500: From MTH.MTH_ITEM_DENORM_D_TMP
1501: Where LEVEL6_FK_KEY is not null and level_NUM = 7;
1502:
1503: -- insert level 5 entries
1504: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1500: From MTH.MTH_ITEM_DENORM_D_TMP
1501: Where LEVEL6_FK_KEY is not null and level_NUM = 7;
1502:
1503: -- insert level 5 entries
1504: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1505: (
1506: HIERARCHY_ID,
1507: ITEM_FK_KEY,
1508: LEVEL1_FK_KEY,
1548: NULL,
1549: NULL,
1550: NULL,
1551: NULL
1552: From MTH.MTH_ITEM_DENORM_D_TMP
1553: Where LEVEL5_FK_KEY is not null and level_NUM = 6;
1554:
1555: -- insert level 4 entries
1556: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1552: From MTH.MTH_ITEM_DENORM_D_TMP
1553: Where LEVEL5_FK_KEY is not null and level_NUM = 6;
1554:
1555: -- insert level 4 entries
1556: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1557: (
1558: HIERARCHY_ID,
1559: ITEM_FK_KEY,
1560: LEVEL1_FK_KEY,
1600: NULL,
1601: NULL,
1602: NULL,
1603: NULL
1604: From MTH.MTH_ITEM_DENORM_D_TMP
1605: Where LEVEL4_FK_KEY is not null and level_NUM = 5;
1606:
1607:
1608: -- insert level 3 entries
1605: Where LEVEL4_FK_KEY is not null and level_NUM = 5;
1606:
1607:
1608: -- insert level 3 entries
1609: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1610: (
1611: HIERARCHY_ID,
1612: ITEM_FK_KEY,
1613: LEVEL1_FK_KEY,
1653: NULL,
1654: NULL,
1655: NULL,
1656: NULL
1657: From MTH.MTH_ITEM_DENORM_D_TMP
1658: Where LEVEL3_FK_KEY is not null and level_NUM = 4;
1659:
1660:
1661: -- insert level 2 entries
1658: Where LEVEL3_FK_KEY is not null and level_NUM = 4;
1659:
1660:
1661: -- insert level 2 entries
1662: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1663: (
1664: HIERARCHY_ID,
1665: ITEM_FK_KEY,
1666: LEVEL1_FK_KEY,
1706: NULL,
1707: NULL,
1708: NULL,
1709: NULL
1710: From MTH.MTH_ITEM_DENORM_D_TMP
1711: Where LEVEL2_FK_KEY is not null and level_NUM = 3;
1712:
1713:
1714:
1712:
1713:
1714:
1715: -- insert level 1 entries
1716: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1717: (
1718: HIERARCHY_ID,
1719: ITEM_FK_KEY,
1720: LEVEL1_FK_KEY,
1760: NULL,
1761: NULL,
1762: NULL,
1763: NULL
1764: From MTH.MTH_ITEM_DENORM_D_TMP
1765: Where LEVEL1_FK_KEY is not null and level_NUM = 2;
1766:
1767:
1768: EXCEPTION
1834: IS
1835: BEGIN
1836:
1837: INSERT
1838: INTO MTH_ITEM_DENORM_D_TMP
1839: (
1840: HIERARCHY_ID,
1841: ITEM_FK_KEY,
1842: LEVEL1_FK_KEY,
1996: B.ITEM_PK_KEY IN
1997: (SELECT c.item_fk_key
1998: FROM mth_item_hierarchy a ,
1999: mth_item_categories_d bb,
2000: mth_item_denorm_d c
2001: WHERE (a.level_fk_key = c.level9_fk_key OR
2002: a.level_fk_key = c.level8_fk_key OR
2003: a.level_fk_key = c.level7_fk_key OR
2004: a.level_fk_key = c.level6_fk_key OR
2048: BEGIN
2049:
2050: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2051: INSERT
2052: INTO MTH_ITEM_DENORM_D_TMP
2053: (
2054: HIERARCHY_ID,
2055: ITEM_FK_KEY,
2056: LEVEL1_FK_KEY,
2238: PROCEDURE UPD_ITEM_DENORM_TMP_UNASSIGNED(p_unassigned_key IN NUMBER,
2239: p_unassigned_category_name IN VARCHAR2)
2240: IS
2241: BEGIN
2242: UPDATE MTH.MTH_ITEM_DENORM_D_TMP
2243: SET level1_fk_key = nvl(level1_fk_key, p_unassigned_key),
2244: level1_name = decode(level1_fk_key, NULL, p_unassigned_category_name, level1_name),
2245: level2_fk_key = nvl(level2_fk_key, p_unassigned_key),
2246: level2_name = Decode(level2_fk_key, NULL, p_unassigned_category_name, level2_name),
2361: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365: FROM MTH_ITEM_DENORM_D_TMP
2366: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367: hierarchy_id = p_hierarchy_id AND
2368: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2420:
2421: --bulk update using forall
2422: FORALL i IN
2423: denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
2424: UPDATE MTH_ITEM_DENORM_D_TMP
2425: SET
2426: level8_fk_key = denorm_rec.level9_fk_key(i),
2427: level7_fk_key = denorm_rec.level7_fk_key(i),
2428: level6_fk_key = denorm_rec.level6_fk_key(i),
2553: decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557: FROM MTH_ITEM_DENORM_D
2558: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559: hierarchy_id = p_hierarchy_id AND
2560: p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561: decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2607:
2608: --bulk update using forall
2609: FORALL i IN
2610: denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
2611: UPDATE MTH_ITEM_DENORM_D
2612: SET
2613: level8_fk_key = denorm_rec.level9_fk_key(i),
2614: level7_fk_key = denorm_rec.level7_fk_key(i),
2615: level6_fk_key = denorm_rec.level6_fk_key(i),
2643:
2644: /* ****************************************************************************
2645: * Procedure :INSERT_UNASS_ITEM_DENORM_TMP *
2646: * Description :This procedure inserts unassigned entry into *
2647: * MTH_ITEM_DENORM_D_TMP when the hierarchy does not exist *
2648: * in item denorm table *
2649: * File Name :MTHITEMDB.PLS *
2650: * Visibility :Private *
2651: * Parameters : *
2660: p_unassigned_category_name IN VARCHAR2)
2661: IS
2662: BEGIN
2663:
2664: INSERT INTO MTH_ITEM_DENORM_D_TMP
2665: ( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
2666: LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
2667: LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY,
2668: LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
2708: PROCEDURE BUILD_LEVEL_1_9_item_denorm (p_hierarchy_id IN NUMBER)
2709: IS
2710: BEGIN
2711:
2712: Insert into mth_item_denorm_d
2713: (
2714: HIERARCHY_ID,
2715: ITEM_FK_KEY,
2716: LEVEL1_FK_KEY,
2756: LEVEL6_NAME,
2757: LEVEL7_NAME,
2758: LEVEL8_NAME,
2759: LEVEL9_NAME
2760: From mth_item_denorm_d
2761: Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
2762: hierarchy_id = p_hierarchy_id;
2763:
2764: -- insert level 8 entries
2761: Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
2762: hierarchy_id = p_hierarchy_id;
2763:
2764: -- insert level 8 entries
2765: Insert into mth_item_denorm_d
2766: (
2767: HIERARCHY_ID,
2768: ITEM_FK_KEY,
2769: LEVEL1_FK_KEY,
2809: LEVEL6_NAME,
2810: LEVEL7_NAME,
2811: LEVEL8_NAME,
2812: NULL
2813: From mth_item_denorm_d
2814: Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
2815: hierarchy_id = p_hierarchy_id;
2816:
2817:
2815: hierarchy_id = p_hierarchy_id;
2816:
2817:
2818: -- insert level 7 entries
2819: Insert into mth_item_denorm_d
2820: (
2821: HIERARCHY_ID,
2822: ITEM_FK_KEY,
2823: LEVEL1_FK_KEY,
2863: LEVEL6_NAME,
2864: LEVEL7_NAME,
2865: NULL,
2866: NULL
2867: From mth_item_denorm_d
2868: Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
2869: hierarchy_id = p_hierarchy_id;
2870:
2871:
2869: hierarchy_id = p_hierarchy_id;
2870:
2871:
2872: -- insert level 6 entries
2873: Insert into mth_item_denorm_d
2874: (
2875: HIERARCHY_ID,
2876: ITEM_FK_KEY,
2877: LEVEL1_FK_KEY,
2917: LEVEL6_NAME,
2918: NULL,
2919: NULL,
2920: NULL
2921: From mth_item_denorm_d
2922: Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
2923: hierarchy_id = p_hierarchy_id;
2924:
2925:
2923: hierarchy_id = p_hierarchy_id;
2924:
2925:
2926: -- insert level 5 entries
2927: Insert into mth_item_denorm_d
2928: (
2929: HIERARCHY_ID,
2930: ITEM_FK_KEY,
2931: LEVEL1_FK_KEY,
2971: NULL,
2972: NULL,
2973: NULL,
2974: NULL
2975: From mth_item_denorm_d
2976: Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
2977: hierarchy_id = p_hierarchy_id;
2978:
2979:
2977: hierarchy_id = p_hierarchy_id;
2978:
2979:
2980: -- insert level 4 entries
2981: Insert into mth_item_denorm_d
2982: (
2983: HIERARCHY_ID,
2984: ITEM_FK_KEY,
2985: LEVEL1_FK_KEY,
3025: NULL,
3026: NULL,
3027: NULL,
3028: NULL
3029: From mth_item_denorm_d
3030: Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
3031: hierarchy_id = p_hierarchy_id;
3032:
3033:
3032:
3033:
3034:
3035: -- insert level 3 entries
3036: Insert into mth_item_denorm_d
3037: (
3038: HIERARCHY_ID,
3039: ITEM_FK_KEY,
3040: LEVEL1_FK_KEY,
3080: NULL,
3081: NULL,
3082: NULL,
3083: NULL
3084: From mth_item_denorm_d
3085: Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
3086: hierarchy_id = p_hierarchy_id;
3087:
3088:
3087:
3088:
3089:
3090: -- insert level 2 entries
3091: Insert into mth_item_denorm_d
3092: (
3093: HIERARCHY_ID,
3094: ITEM_FK_KEY,
3095: LEVEL1_FK_KEY,
3135: NULL,
3136: NULL,
3137: NULL,
3138: NULL
3139: From mth_item_denorm_d
3140: Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
3141: hierarchy_id = p_hierarchy_id;
3142:
3143:
3143:
3144:
3145:
3146: -- insert level 1 entries
3147: Insert into mth_item_denorm_d
3148: (
3149: HIERARCHY_ID,
3150: ITEM_FK_KEY,
3151: LEVEL1_FK_KEY,
3191: NULL,
3192: NULL,
3193: NULL,
3194: NULL
3195: From mth_item_denorm_d
3196: Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
3197: hierarchy_id = p_hierarchy_id;
3198:
3199:
3220: PROCEDURE BUILD_LVL_1_9_item_denorm_tmp (p_hierarchy_id IN NUMBER)
3221: IS
3222: BEGIN
3223:
3224: Insert into MTH_ITEM_DENORM_D_TMP
3225: (
3226: HIERARCHY_ID,
3227: ITEM_FK_KEY,
3228: LEVEL1_FK_KEY,
3268: LEVEL6_NAME,
3269: LEVEL7_NAME,
3270: LEVEL8_NAME,
3271: LEVEL9_NAME
3272: From MTH_ITEM_DENORM_D_TMP
3273: Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
3274: hierarchy_id = p_hierarchy_id;
3275:
3276:
3274: hierarchy_id = p_hierarchy_id;
3275:
3276:
3277: -- insert level 8 entries
3278: Insert into MTH_ITEM_DENORM_D_TMP
3279: (
3280: HIERARCHY_ID,
3281: ITEM_FK_KEY,
3282: LEVEL1_FK_KEY,
3322: LEVEL6_NAME,
3323: LEVEL7_NAME,
3324: LEVEL8_NAME,
3325: NULL
3326: From MTH_ITEM_DENORM_D_TMP
3327: Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
3328: hierarchy_id = p_hierarchy_id;
3329:
3330:
3328: hierarchy_id = p_hierarchy_id;
3329:
3330:
3331: -- insert level 7 entries
3332: Insert into MTH_ITEM_DENORM_D_TMP
3333: (
3334: HIERARCHY_ID,
3335: ITEM_FK_KEY,
3336: LEVEL1_FK_KEY,
3376: LEVEL6_NAME,
3377: LEVEL7_NAME,
3378: NULL,
3379: NULL
3380: From MTH_ITEM_DENORM_D_TMP
3381: Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
3382: hierarchy_id = p_hierarchy_id;
3383:
3384:
3382: hierarchy_id = p_hierarchy_id;
3383:
3384:
3385: -- insert level 6 entries
3386: Insert into MTH_ITEM_DENORM_D_TMP
3387: (
3388: HIERARCHY_ID,
3389: ITEM_FK_KEY,
3390: LEVEL1_FK_KEY,
3430: LEVEL6_NAME,
3431: NULL,
3432: NULL,
3433: NULL
3434: From MTH_ITEM_DENORM_D_TMP
3435: Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
3436: hierarchy_id = p_hierarchy_id;
3437:
3438:
3436: hierarchy_id = p_hierarchy_id;
3437:
3438:
3439: -- insert level 5 entries
3440: Insert into MTH_ITEM_DENORM_D_TMP
3441: (
3442: HIERARCHY_ID,
3443: ITEM_FK_KEY,
3444: LEVEL1_FK_KEY,
3484: NULL,
3485: NULL,
3486: NULL,
3487: NULL
3488: From MTH_ITEM_DENORM_D_TMP
3489: Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
3490: hierarchy_id = p_hierarchy_id;
3491:
3492:
3490: hierarchy_id = p_hierarchy_id;
3491:
3492:
3493: -- insert level 4 entries
3494: Insert into MTH_ITEM_DENORM_D_TMP
3495: (
3496: HIERARCHY_ID,
3497: ITEM_FK_KEY,
3498: LEVEL1_FK_KEY,
3538: NULL,
3539: NULL,
3540: NULL,
3541: NULL
3542: From MTH_ITEM_DENORM_D_TMP
3543: Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
3544: hierarchy_id = p_hierarchy_id;
3545:
3546:
3545:
3546:
3547:
3548: -- insert level 3 entries
3549: Insert into MTH_ITEM_DENORM_D_TMP
3550: (
3551: HIERARCHY_ID,
3552: ITEM_FK_KEY,
3553: LEVEL1_FK_KEY,
3593: NULL,
3594: NULL,
3595: NULL,
3596: NULL
3597: From MTH_ITEM_DENORM_D_TMP
3598: Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
3599: hierarchy_id = p_hierarchy_id;
3600:
3601:
3600:
3601:
3602:
3603: -- insert level 2 entries
3604: Insert into MTH_ITEM_DENORM_D_TMP
3605: (
3606: HIERARCHY_ID,
3607: ITEM_FK_KEY,
3608: LEVEL1_FK_KEY,
3648: NULL,
3649: NULL,
3650: NULL,
3651: NULL
3652: From MTH_ITEM_DENORM_D_TMP
3653: Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
3654: hierarchy_id = p_hierarchy_id;
3655:
3656:
3656:
3657:
3658:
3659: -- insert level 1 entries
3660: Insert into MTH_ITEM_DENORM_D_TMP
3661: (
3662: HIERARCHY_ID,
3663: ITEM_FK_KEY,
3664: LEVEL1_FK_KEY,
3704: NULL,
3705: NULL,
3706: NULL,
3707: NULL
3708: From MTH_ITEM_DENORM_D_TMP
3709: Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
3710: hierarchy_id = p_hierarchy_id;
3711:
3712:
3742:
3743: v_log_to_date := sysdate;
3744: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3745:
3746: INSERT INTO mth_item_denorm_d
3747: (hierarchy_id,
3748: item_fk_key,
3749: level9_fk_key,
3750: level8_fk_key,
3797: v_unassigned_val,
3798: v_unassigned_val,
3799: v_log_to_date,
3800: v_log_to_date
3801: FROM mth_item_denorm_d_tmp tmp
3802: WHERE tmp.level_num < 10 AND
3803: tmp.hierarchy_id = p_hierarchy_id AND
3804: NOT EXISTS
3805: (SELECT 1
3802: WHERE tmp.level_num < 10 AND
3803: tmp.hierarchy_id = p_hierarchy_id AND
3804: NOT EXISTS
3805: (SELECT 1
3806: FROM mth_item_denorm_d mid
3807: WHERE mid.hierarchy_id = tmp.hierarchy_id AND
3808: ((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
3809: OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
3810: OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
3840:
3841: v_log_to_date := sysdate;
3842: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3843:
3844: Merge INTO mth_item_denorm_d mid USING (SELECT hierarchy_id,
3845: item_fk_key,
3846: level9_fk_key,
3847: level8_fk_key,
3848: level7_fk_key,
3862: level4_name,
3863: level3_name,
3864: level2_name,
3865: level1_name
3866: FROM (SELECT * FROM mth_item_denorm_d_tmp WHERE level_num < 10 )) tmp
3867: ON (mid.hierarchy_id = p_hierarchy_id AND tmp.hierarchy_id = mid.hierarchy_id
3868: AND ((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
3869: OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
3870: OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
3988: decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989: decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990: decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991: decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992: FROM MTH_ITEM_DENORM_D_TMP
3993: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
3994: GROUP BY hierarchy_id) id,
3995: mth_dim_hierarchy mdh
3996: WHERE id.hierarchy_id = mdh.hierarchy_id;
4032:
4033:
4034: -- Step 1: If the table is empty, call init procedure
4035: /*
4036: SELECT 1 INTO v_num_rows FROM mth_item_denorm_d WHERE ROWNUM = 1;
4037:
4038: IF (v_num_rows IS NULL) THEN
4039: ITEM_DIM_LOAD_DENORM();
4040: RETURN;
4042: */
4043:
4044: /*Step 2*/
4045: -- Call mth_run_log_pre_load
4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4043:
4044: /*Step 2*/
4045: -- Call mth_run_log_pre_load
4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051: v_log_from_date,v_log_to_date) ;
4046: -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047: mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048: MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049: NULL,0,v_log_to_date);
4050: mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051: v_log_from_date,v_log_to_date) ;
4052:
4053: -- Step 3: Load items impacted by the changed categories entries
4054: -- from item hierarchy since last run into item denorm temp table
4093: decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094: decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095: decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096: AS num_levels
4097: FROM MTH_ITEM_DENORM_D ID
4098: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4099: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )
4100: -- WHERE ROWNUM = 1)
4101: WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
4099: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )
4100: -- WHERE ROWNUM = 1)
4101: WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
4102: DH.dimension_name= 'ITEM' AND
4103: EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
4104: WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4105: DH.dimension_name= 'ITEM' AND
4106: ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
4107: */
4173: decode(level3_fk_key,v_unassigned_key,0,NULL,0,1) +
4174: decode(level2_fk_key,v_unassigned_key,0,NULL,0,1) +
4175: decode(level1_fk_key,v_unassigned_key,0,NULL,0,1))
4176: INTO v_num_levels
4177: FROM MTH_ITEM_DENORM_D
4178: WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4179: level_num = 10 AND
4180: hierarchy_id = r_hid_and_levels.hierarchy_id;
4181:
4194: -- 7.2: Delete data in the item denorm table for entries existed in tmp
4195: -- table if the item denorm table has data iff v_num_levels is not null
4196: v_num_rows_deleted := FALSE;
4197: IF ( v_num_levels IS NOT NULL ) THEN
4198: DELETE FROM mth_item_denorm_d id
4199: WHERE id.hierarchy_id = r_hid_and_levels.hierarchy_id AND
4200: level_num = 10 AND
4201: item_fk_key IN (SELECT idt.item_fk_key
4202: FROM mth_item_denorm_d_tmp idt
4198: DELETE FROM mth_item_denorm_d id
4199: WHERE id.hierarchy_id = r_hid_and_levels.hierarchy_id AND
4200: level_num = 10 AND
4201: item_fk_key IN (SELECT idt.item_fk_key
4202: FROM mth_item_denorm_d_tmp idt
4203: WHERE idt.hierarchy_id = id.hierarchy_id AND
4204: idt.level_num = 10);
4205: v_num_rows_deleted := (SQL%ROWCOUNT > 0);
4206: END IF;
4214: -- Double check if the hierarchy does not exist in item denorm
4215:
4216: BEGIN
4217: SELECT 1 INTO v_num_rows
4218: FROM mth_item_denorm_d
4219: WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4220: level_num = 10 AND
4221: ROWNUM = 1;
4222:
4274: v_rebld_level_1_to_9 := FALSE;
4275: IF ( v_num_rows_deleted OR
4276: v_num_levels IS NOT NULL AND v_num_levels > 0 AND
4277: r_hid_and_levels.max_level > v_num_levels ) THEN
4278: DELETE FROM mth_item_denorm_d
4279: WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4280: level_num < 10;
4281: v_rebld_level_1_to_9 := TRUE;
4282: END IF;
4282: END IF;
4283:
4284: --
4285: -- 7.6: Copy data (level 10 so far) from tmp to item denorm table
4286: INSERT INTO MTH_ITEM_DENORM_D
4287: ( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
4288: LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
4289: LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY, CREATED_BY,
4290: LAST_UPDATE_LOGIN, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
4297: v_unassigned_key, v_unassigned_key, v_log_to_date, v_log_to_date,
4298: LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
4299: LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
4300: LEVEL1_NAME
4301: FROM MTH_ITEM_DENORM_D_TMP
4302: WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4303: level_num = 10;
4304:
4305:
4354:
4355:
4356:
4357: -- Step 8: Run mth_run_log_post_load to update run log entry
4358: mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',
4359: MTH_UTIL_PKG.MTH_UA_GET_VAL());
4360:
4361:
4362: COMMIT;