[Home] [Help]
1119: fh.child_fin_cat_id
1120: FROM FII_FULL_FIN_ITEM_HIERS fh
1121: WHERE fh.parent_fin_cat_id IN
1122: (SELECT ph.parent_fin_cat_id
1123: FROM FII_FIN_ITEM_HIERARCHIES ph
1124: WHERE ph.is_leaf_flag = 'Y');
1125:
1126: IF (FIIDIM_Debug) THEN
1127: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
1127: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_CAT_MAPPINGS_GT');
1128: END IF;
1129:
1130: --Then, insert self-mapping records for all nodes in pruned hierarchy
1131: --FII_FIN_ITEM_HIERARCHIES. Note we should exclude all self leaf
1132: --records since they are inserted in the previous step.
1133: g_phase := 'insert self-mapping records for all nodes in pruned hierarchy';
1134:
1135: INSERT INTO FII_FIN_CAT_MAPPINGS_GT
1136: (parent_fin_cat_id,
1137: child_fin_cat_id)
1138: SELECT parent_fin_cat_id,
1139: child_fin_cat_id
1140: FROM FII_FIN_ITEM_HIERARCHIES
1141: WHERE child_flex_value_set_id = G_MASTER_VALUE_SET_ID
1142: AND parent_fin_cat_id = child_fin_cat_id
1143: AND is_leaf_flag = 'N';
1144:
1583: 'Handle_Unenabled_DIM');
1584: END IF;
1585:
1586: FII_UTIL.truncate_table ('FII_FULL_FIN_ITEM_HIERS', 'FII', g_retcode);
1587: FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1588: FII_UTIL.truncate_table ('FII_FIN_CAT_MAPPINGS', 'FII', g_retcode);
1589: FII_UTIL.truncate_table ('FII_FIN_CAT_TYPE_ASSGNS', 'FII', g_retcode);
1590: FII_UTIL.truncate_table ('FII_FIN_ITEM_LEAF_HIERS', 'FII', g_retcode);
1591: FII_UTIL.truncate_table ('FII_FIN_CAT_LEAF_MAPS', 'FII', g_retcode);
1759: END IF;
1760:
1761: commit;
1762:
1763: -- Since leaf nodes are always included we copy FII_FIN_ITEM_HIER_GT to FII_FIN_ITEM_HIERARCHIES
1764: --Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final dimension table for DBI6.0
1765: g_phase := 'Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final full dimension table for DBI 6.0';
1766:
1767: FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1763: -- Since leaf nodes are always included we copy FII_FIN_ITEM_HIER_GT to FII_FIN_ITEM_HIERARCHIES
1764: --Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final dimension table for DBI6.0
1765: g_phase := 'Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final full dimension table for DBI 6.0';
1766:
1767: FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1768:
1769: INSERT /*+ APPEND */ INTO FII_FIN_ITEM_HIERARCHIES (
1770: parent_level,
1771: parent_fin_cat_id,
1765: g_phase := 'Copy FII_FIN_ITEM_HIER_GT hierarchy table to the final full dimension table for DBI 6.0';
1766:
1767: FII_UTIL.truncate_table ('FII_FIN_ITEM_HIERARCHIES', 'FII', g_retcode);
1768:
1769: INSERT /*+ APPEND */ INTO FII_FIN_ITEM_HIERARCHIES (
1770: parent_level,
1771: parent_fin_cat_id,
1772: next_level,
1773: next_level_fin_cat_id,
1800: FII_LOGIN_ID
1801: FROM FII_FIN_ITEM_HIER_GT;
1802:
1803: IF (FIIDIM_Debug) THEN
1804: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
1805: END IF;
1806:
1807: -- We have added an update statement on FII_FIN_ITEM_LEAF_HIERS. Hence, moved gathering statistics
1808: -- for FII_FIN_ITEM_LEAF_HIERS table and its mlog at the end of procedure.
1810: --Call FND_STATS to collect statistics after re-populating the tables.
1811: --Will seed this in RSG
1812: -- FND_STATS.gather_table_stats
1813: -- (ownname => g_schema_name,
1814: -- tabname => 'FII_FIN_ITEM_HIERARCHIES');
1815:
1816: --to avoid ORA-12838: cannot read/modify an object after modifying
1817: --it in parallel (due to the hint APPEND)
1818: commit;
2270: --Copy FII_FIN_ITEM_HIER_GT to the final (pruned) dimension table for DBI6.0
2271:
2272: -- Incremental Dimension Maintence
2273: -- All data is now in the temporary table FII_FIN_ITEM_LEAF_HIERS,
2274: -- we need to maintain the permanent table FII_FIN_ITEM_HIERARCHIES
2275: -- by diffing the 2 tables.
2276: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
2277:
2278: g_phase := 'DELETE FROM FII_FIN_ITEM_HIERARCHIES';
2274: -- we need to maintain the permanent table FII_FIN_ITEM_HIERARCHIES
2275: -- by diffing the 2 tables.
2276: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
2277:
2278: g_phase := 'DELETE FROM FII_FIN_ITEM_HIERARCHIES';
2279:
2280: DELETE FROM FII_FIN_ITEM_HIERARCHIES
2281: WHERE
2282: (parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2276: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
2277:
2278: g_phase := 'DELETE FROM FII_FIN_ITEM_HIERARCHIES';
2279:
2280: DELETE FROM FII_FIN_ITEM_HIERARCHIES
2281: WHERE
2282: (parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2283: next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2284: parent_flex_value_set_id, child_flex_value_set_id) IN
2284: parent_flex_value_set_id, child_flex_value_set_id) IN
2285: (SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2286: next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2287: parent_flex_value_set_id, child_flex_value_set_id
2288: FROM FII_FIN_ITEM_HIERARCHIES
2289: MINUS
2290: SELECT parent_level, parent_fin_cat_id, next_level, next_level_fin_cat_id,
2291: next_level_is_leaf, is_leaf_flag, child_level, child_fin_cat_id,
2292: parent_flex_value_set_id, child_flex_value_set_id
2292: parent_flex_value_set_id, child_flex_value_set_id
2293: FROM FII_FIN_ITEM_HIER_GT);
2294:
2295: IF (FIIDIM_Debug) THEN
2296: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_HIERARCHIES');
2297: END IF;
2298:
2299: g_phase := 'Insert into FII_FIN_ITEM_HIERARCHIES';
2300:
2295: IF (FIIDIM_Debug) THEN
2296: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FIN_ITEM_HIERARCHIES');
2297: END IF;
2298:
2299: g_phase := 'Insert into FII_FIN_ITEM_HIERARCHIES';
2300:
2301: Insert into FII_FIN_ITEM_HIERARCHIES (
2302: parent_level,
2303: parent_fin_cat_id,
2297: END IF;
2298:
2299: g_phase := 'Insert into FII_FIN_ITEM_HIERARCHIES';
2300:
2301: Insert into FII_FIN_ITEM_HIERARCHIES (
2302: parent_level,
2303: parent_fin_cat_id,
2304: next_level,
2305: next_level_fin_cat_id,
2345: FII_USER_ID,
2346: SYSDATE,
2347: FII_USER_ID,
2348: FII_LOGIN_ID
2349: FROM FII_FIN_ITEM_HIERARCHIES);
2350:
2351: IF (FIIDIM_Debug) THEN
2352: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
2353: END IF;
2348: FII_LOGIN_ID
2349: FROM FII_FIN_ITEM_HIERARCHIES);
2350:
2351: IF (FIIDIM_Debug) THEN
2352: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_HIERARCHIES');
2353: END IF;
2354:
2355: -- We have added an update statement on FII_FIN_ITEM_LEAF_HIERS. Hence, moved gathering statistics
2356: -- for FII_FIN_ITEM_LEAF_HIERS table at the end of procedure.
2358: --Call FND_STATS to collect statistics after re-populating the tables.
2359: --Will seed this in RSG
2360: -- FND_STATS.gather_table_stats
2361: -- (ownname => g_schema_name,
2362: -- tabname => 'FII_FIN_ITEM_HIERARCHIES');
2363:
2364: --================================================================--
2365: --Populate FII_FIN_CAT_MAPPINGS table
2366: g_phase := 'Populate FII_FIN_CAT_MAPPINGS_GT table';