[Home] [Help]
961: (PARENT_USER_DIM1_ID,
962: CHILD_USER_DIM1_ID)
963: SELECT fh.parent_value_id,
964: fh.child_value_id
965: FROM FII_FULL_UDD1_HIERS fh
966: WHERE fh.parent_value_id IN
967: (SELECT ph.parent_value_id
968: FROM FII_UDD1_HIERARCHIES ph
969: WHERE ph.is_leaf_flag = 'Y');
1111: -- before also since truncation of the tables does not let
1112: -- incremental refresh of MV happen.
1113: -- If the dimension hierarchy table has 1 record then the dimension
1114: -- was disabled previously.
1115: select count(*) into l_count from fii_full_udd1_hiers;
1116:
1117: IF(l_count > 1 OR l_count = 0) THEN
1118: -- Incase the dimension hierarchy table had more than 1 record
1119: -- this means the dimension was enabled previously and it has been
1120: -- disabled now, in which case initial refresh of MV should happen
1121: -- so it is ok to truncate the tables
1122:
1123: g_phase := 'Truncate dimension hierarchy tables';
1124: FII_UTIL.truncate_table ('FII_FULL_UDD1_HIERS', 'FII', g_retcode);
1125: FII_UTIL.truncate_table ('FII_UDD1_HIERARCHIES', 'FII', g_retcode);
1126: FII_UTIL.truncate_table ('FII_UDD1_MAPPINGS', 'FII', g_retcode);
1127:
1128: g_phase := 'Inserting UNASSIGNED record in Full hierarchy';
1126: FII_UTIL.truncate_table ('FII_UDD1_MAPPINGS', 'FII', g_retcode);
1127:
1128: g_phase := 'Inserting UNASSIGNED record in Full hierarchy';
1129:
1130: INSERT INTO FII_FULL_UDD1_HIERS
1131: (parent_level,
1132: parent_value_id,
1133: next_level,
1134: next_level_value_id,
1266:
1267: --Copy TMP hierarchy table to the final dimension table
1268: g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1269:
1270: FII_UTIL.truncate_table ('FII_FULL_UDD1_HIERS', 'FII', g_retcode);
1271:
1272: INSERT /*+ APPEND */ INTO FII_FULL_UDD1_HIERS (
1273: parent_level,
1274: parent_value_id,
1268: g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1269:
1270: FII_UTIL.truncate_table ('FII_FULL_UDD1_HIERS', 'FII', g_retcode);
1271:
1272: INSERT /*+ APPEND */ INTO FII_FULL_UDD1_HIERS (
1273: parent_level,
1274: parent_value_id,
1275: next_level,
1276: next_level_value_id,
1303: FII_LOGIN_ID
1304: FROM FII_UDD1_HIER_GT;
1305:
1306: IF (FIIDIM_Debug) THEN
1307: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows in FII_FULL_UDD1_HIERS');
1308: END IF;
1309:
1310: --------------------------------------------------------
1311: -- Gather statistics for the use of cost-based optimizer
1314: --Call FND_STATS to collect statistics after re-populating the tables.
1315: --for the full dimension table since it will be used later
1316: FND_STATS.gather_table_stats
1317: (ownname => g_schema_name,
1318: tabname => 'FII_FULL_UDD1_HIERS');
1319:
1320: --==============================================================--
1321:
1322: --Delete/Update FII_UDD1_HIER_GT for pruned hierarchy table
1522:
1523: Flatten_UD1_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1524:
1525: g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1526: FII_UTIL.truncate_table ('FII_FULL_UDD1_HIERS', 'FII', g_retcode);
1527:
1528: Insert into FII_FULL_UDD1_HIERS (
1529: parent_level,
1530: parent_value_id,
1524:
1525: g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1526: FII_UTIL.truncate_table ('FII_FULL_UDD1_HIERS', 'FII', g_retcode);
1527:
1528: Insert into FII_FULL_UDD1_HIERS (
1529: parent_level,
1530: parent_value_id,
1531: next_level,
1532: next_level_value_id,
1558: FII_LOGIN_ID
1559: FROM FII_UDD1_HIER_GT;
1560:
1561: IF (FIIDIM_Debug) THEN
1562: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_UDD1_HIERS');
1563: END IF;
1564:
1565: --------------------------------------------------------
1566: -- Gather statistics for the use of cost-based optimizer
1568: --Call FND_STATS to collect statistics after re-populating the tables.
1569: --for the full dimension table since it will be used later
1570: FND_STATS.gather_table_stats
1571: (ownname => g_schema_name,
1572: tabname => 'FII_FULL_UDD1_HIERS');
1573:
1574: --==============================================================--
1575:
1576: --Delete/Update FII_UDD1_HIER_GT for pruned hierarchy table