[Home] [Help]
964: fh.child_value_id
965: FROM FII_FULL_UDD2_HIERS fh
966: WHERE fh.parent_value_id IN
967: (SELECT ph.parent_value_id
968: FROM FII_UDD2_HIERARCHIES ph
969: WHERE ph.is_leaf_flag = 'Y');
970:
971: IF (FIIDIM_Debug) THEN
972: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_MAPPINGS_GT');
973: END IF;
974:
975:
976: --Then, insert self-mapping records for all nodes in pruned hierarchy
977: --FII_UDD2_HIERARCHIES. Note we should exclude all self leaf
978: --records since they are inserted in the previous step.
979: g_phase := 'insert self-mapping records for all nodes in pruned hierarchy';
980:
981: INSERT INTO FII_UDD2_MAPPING_GT
982: (PARENT_USER_DIM2_ID,
983: CHILD_USER_DIM2_ID)
984: SELECT parent_value_id,
985: child_value_id
986: FROM FII_UDD2_HIERARCHIES
987: WHERE child_flex_value_set_id = G_MASTER_VALUE_SET_ID
988: AND parent_value_id = child_value_id
989: AND is_leaf_flag = 'N';
990:
1014:
1015: END Get_UD2_Mapping_GT;
1016:
1017: -- **************************************************************************
1018: -- Populate the pruned User Defined Dimension2 hierarchy FII_UDD2_HIERARCHIES by deleting from
1019: -- FII_UDD2_HIER_GT (full version) the LVS records
1020:
1021: PROCEDURE Get_Pruned_UD2_GT IS
1022:
1119: -- so it is ok to truncate the tables
1120:
1121: g_phase := 'Truncate dimension hierarchy tables';
1122: FII_UTIL.truncate_table ('FII_FULL_UDD2_HIERS', 'FII', g_retcode);
1123: FII_UTIL.truncate_table ('FII_UDD2_HIERARCHIES', 'FII', g_retcode);
1124: FII_UTIL.truncate_table ('FII_UDD2_MAPPINGS', 'FII', g_retcode);
1125:
1126: g_phase := 'Inserting UNASSIGNED record in Full hierarchy';
1127:
1159: FII_LOGIN_ID);
1160:
1161: g_phase := 'Inserting UNASSIGNED record in Pruned hierarchy';
1162:
1163: INSERT INTO FII_UDD2_HIERARCHIES
1164: (parent_level,
1165: parent_value_id,
1166: next_level,
1167: next_level_value_id,
1321:
1322: --Copy FII_UDD2_HIER_GT to the final (pruned) dimension table
1323: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1324:
1325: FII_UTIL.truncate_table ('FII_UDD2_HIERARCHIES', 'FII', g_retcode);
1326:
1327: INSERT /*+ APPEND */ INTO FII_UDD2_HIERARCHIES (
1328: parent_level,
1329: parent_value_id,
1323: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1324:
1325: FII_UTIL.truncate_table ('FII_UDD2_HIERARCHIES', 'FII', g_retcode);
1326:
1327: INSERT /*+ APPEND */ INTO FII_UDD2_HIERARCHIES (
1328: parent_level,
1329: parent_value_id,
1330: next_level,
1331: next_level_value_id,
1362: FII_LOGIN_ID
1363: FROM FII_UDD2_HIER_GT;
1364:
1365: IF (FIIDIM_Debug) THEN
1366: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1367: END IF;
1368:
1369: -- This will be in RSG data
1370: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1366: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1367: END IF;
1368:
1369: -- This will be in RSG data
1370: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1371: FND_STATS.gather_table_stats
1372: (ownname => g_schema_name,
1373: tabname => 'FII_UDD2_HIERARCHIES');
1374:
1369: -- This will be in RSG data
1370: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1371: FND_STATS.gather_table_stats
1372: (ownname => g_schema_name,
1373: tabname => 'FII_UDD2_HIERARCHIES');
1374:
1375: g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1376: FND_STATS.gather_table_stats
1377: (ownname => g_schema_name,
1371: FND_STATS.gather_table_stats
1372: (ownname => g_schema_name,
1373: tabname => 'FII_UDD2_HIERARCHIES');
1374:
1375: g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1376: FND_STATS.gather_table_stats
1377: (ownname => g_schema_name,
1378: tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1379:
1374:
1375: g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1376: FND_STATS.gather_table_stats
1377: (ownname => g_schema_name,
1378: tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1379:
1380: --to avoid ORA-12838: cannot read/modify an object after modifying
1381: --it in parallel (due to the hint APPEND)
1382: commit;
1570: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1571:
1572: -- Incremental Dimension Maintence
1573: -- All data is now in the temporary table FII_UDD2_HIER_GT,
1574: -- we need to maintain the permanent table FII_UDD2_HIERARCHIES
1575: -- by diffing the 2 tables.
1576: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1577:
1578: DELETE FROM FII_UDD2_HIERARCHIES
1574: -- we need to maintain the permanent table FII_UDD2_HIERARCHIES
1575: -- by diffing the 2 tables.
1576: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1577:
1578: DELETE FROM FII_UDD2_HIERARCHIES
1579: WHERE
1580: (parent_level, parent_value_id, next_level,
1581: next_level_value_id,
1582: next_level_is_leaf_flag, is_leaf_flag, child_level,
1588: next_level, next_level_value_id,
1589: next_level_is_leaf_flag, is_leaf_flag, child_level,
1590: child_value_id,parent_flex_value_set_id,
1591: child_flex_value_set_id, NVL(next_level_value_sort_order, -92883)
1592: FROM FII_UDD2_HIERARCHIES
1593: MINUS
1594: SELECT parent_level, parent_value_id,
1595: next_level, next_level_value_id,
1596: next_level_is_leaf_flag, is_leaf_flag,
1600: FROM FII_UDD2_HIER_GT);
1601:
1602:
1603: IF (FIIDIM_Debug) THEN
1604: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_UDD2_HIERARCHIES');
1605: END IF;
1606:
1607: Insert into FII_UDD2_HIERARCHIES (
1608: parent_level,
1603: IF (FIIDIM_Debug) THEN
1604: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_UDD2_HIERARCHIES');
1605: END IF;
1606:
1607: Insert into FII_UDD2_HIERARCHIES (
1608: parent_level,
1609: parent_value_id,
1610: next_level,
1611: next_level_value_id,
1657: FII_USER_ID,
1658: SYSDATE,
1659: FII_USER_ID,
1660: FII_LOGIN_ID
1661: FROM FII_UDD2_HIERARCHIES);
1662:
1663: IF (FIIDIM_Debug) THEN
1664: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1665: END IF;
1660: FII_LOGIN_ID
1661: FROM FII_UDD2_HIERARCHIES);
1662:
1663: IF (FIIDIM_Debug) THEN
1664: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1665: END IF;
1666:
1667: -- This will be in RSG data
1668: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1664: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD2_HIERARCHIES');
1665: END IF;
1666:
1667: -- This will be in RSG data
1668: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1669: FND_STATS.gather_table_stats
1670: (ownname => g_schema_name,
1671: tabname => 'FII_UDD2_HIERARCHIES');
1672:
1667: -- This will be in RSG data
1668: g_phase := 'gather_table_stats for FII_UDD2_HIERARCHIES';
1669: FND_STATS.gather_table_stats
1670: (ownname => g_schema_name,
1671: tabname => 'FII_UDD2_HIERARCHIES');
1672:
1673: -- Bug 4200473. Not to analyze MLOG in incremental run.
1674: -- As per performance teams suggestions.
1675:
1672:
1673: -- Bug 4200473. Not to analyze MLOG in incremental run.
1674: -- As per performance teams suggestions.
1675:
1676: --g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1677: --FND_STATS.gather_table_stats
1678: -- (ownname => g_schema_name,
1679: -- tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1680: --=============================================================--
1675:
1676: --g_phase := 'gather_table_stats for MLOG$_FII_UDD2_HIERARCHIES';
1677: --FND_STATS.gather_table_stats
1678: -- (ownname => g_schema_name,
1679: -- tabname => 'MLOG$_FII_UDD2_HIERARCHIES');
1680: --=============================================================--
1681:
1682: --Populate FII_UDD2_MAPPINGS table
1683: g_phase := 'Populate FII_UDD2_MAPPINGS_GT table';