eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MTH_ITEM_DIMENSION_PKG dependencies on MTH_ITEM_DENORM_D

Line 57: INTO MTH_ITEM_DENORM_D

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,

Line 255: mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');

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

Line 259: UPDATE MTH_ITEM_DENORM_D

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),

Line 282: Insert into mth_item_denorm_d

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,

Line 330: From mth_item_denorm_d

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

Line 334: Insert into mth_item_denorm_d

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,

Line 382: From mth_item_denorm_d

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

Line 386: Insert into mth_item_denorm_d

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,

Line 434: From mth_item_denorm_d

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

Line 438: Insert into mth_item_denorm_d

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,

Line 486: From mth_item_denorm_d

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

Line 490: Insert into mth_item_denorm_d

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,

Line 538: From mth_item_denorm_d

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

Line 542: Insert into mth_item_denorm_d

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,

Line 590: From mth_item_denorm_d

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

Line 595: Insert into 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,

Line 643: From mth_item_denorm_d

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

Line 648: Insert into 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,

Line 696: From mth_item_denorm_d

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:

Line 702: Insert into mth_item_denorm_d

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,

Line 750: From mth_item_denorm_d

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

Line 892: FROM MTH.MTH_ITEM_DENORM_D_TMP

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 896: AND level9_fk_key IS NOT NULL

Line 923: UPDATE MTH.MTH_ITEM_DENORM_D_TMP

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),

Line 1005: INTO MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1203: mth_hrchy_balance_load('MTH.MTH_ITEM_DENORM_D_TMP');

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),

Line 1206: UPDATE MTH.MTH_ITEM_DENORM_D_TMP

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),

Line 1229: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1277: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1281: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1329: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1333: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1381: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1385: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1433: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1437: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1485: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1489: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1537: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1542: Insert into 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,

Line 1590: From MTH.MTH_ITEM_DENORM_D_TMP

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;
1592:
1593:
1594: -- insert level 2 entries

Line 1595: Insert into MTH.MTH_ITEM_DENORM_D_TMP

1591: Where LEVEL3_FK_KEY is not null and level_NUM = 4;
1592:
1593:
1594: -- insert level 2 entries
1595: Insert into MTH.MTH_ITEM_DENORM_D_TMP
1596: (
1597: HIERARCHY_ID,
1598: ITEM_FK_KEY,
1599: LEVEL1_FK_KEY,

Line 1643: From MTH.MTH_ITEM_DENORM_D_TMP

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:

Line 1649: Insert into MTH.MTH_ITEM_DENORM_D_TMP

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,

Line 1697: From MTH.MTH_ITEM_DENORM_D_TMP

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

Line 1715: DELETE FROM mth_item_denorm_d d

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

Line 1718: FROM mth.mth_item_denorm_d_tmp t

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

Line 1746: insert into mth_item_denorm_d

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,
1749: LEVEL_NUM,
1750: ITEM_FK_KEY,

Line 1794: FROM MTH.MTH_ITEM_DENORM_D_TMP t

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

Line 1797: FROM mth_item_denorm_d d

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