[Home] [Help]
1005: END Get_level_populated;
1006:
1007:
1008: -- **************************************************************************
1009: -- Populate the pruned COMPANY hierarchy FII_company_HIERARCHIES by deleting from
1010: -- FII_company_HIER_GT (full version) the LVS records
1011:
1012: PROCEDURE Get_Pruned_CO_GT IS
1013:
1110: -- so it is ok to truncate the tables
1111:
1112: g_phase := 'Truncate dimension hierarchy tables';
1113: FII_UTIL.truncate_table ('FII_FULL_COMPANY_HIERS', 'FII', g_retcode);
1114: FII_UTIL.truncate_table ('FII_COMPANY_HIERARCHIES', 'FII', g_retcode);
1115:
1116: INSERT INTO FII_FULL_COMPANY_HIERS
1117: (parent_level,
1118: parent_company_id,
1146: FII_USER_ID,
1147: FII_LOGIN_ID);
1148:
1149:
1150: INSERT INTO FII_COMPANY_HIERARCHIES
1151: (parent_level,
1152: parent_company_id,
1153: next_level,
1154: next_level_company_id,
1298:
1299: --Copy FII_company_HIER_GT to the final (pruned) dimension table
1300: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1301:
1302: FII_UTIL.truncate_table ('FII_COMPANY_HIERARCHIES', 'FII', g_retcode);
1303:
1304: INSERT /*+ APPEND */ INTO FII_COMPANY_HIERARCHIES (
1305: parent_level,
1306: parent_company_id,
1300: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1301:
1302: FII_UTIL.truncate_table ('FII_COMPANY_HIERARCHIES', 'FII', g_retcode);
1303:
1304: INSERT /*+ APPEND */ INTO FII_COMPANY_HIERARCHIES (
1305: parent_level,
1306: parent_company_id,
1307: next_level,
1308: next_level_company_id,
1347: FII_LOGIN_ID
1348: FROM FII_COMPANY_HIER_GT;
1349:
1350: IF (FIIDIM_Debug) THEN
1351: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
1352: END IF;
1353:
1354: -- This will be in RSG data
1355: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1351: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
1352: END IF;
1353:
1354: -- This will be in RSG data
1355: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1356: FND_STATS.gather_table_stats
1357: (ownname => g_schema_name,
1358: tabname => 'FII_COMPANY_HIERARCHIES');
1359:
1354: -- This will be in RSG data
1355: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1356: FND_STATS.gather_table_stats
1357: (ownname => g_schema_name,
1358: tabname => 'FII_COMPANY_HIERARCHIES');
1359:
1360: g_phase := 'gather_table_stats for MLOG$_FII_COMPANY_HIERARCH';
1361: FND_STATS.gather_table_stats
1362: (ownname => g_schema_name,
1505: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1506:
1507: -- Incremental Dimension Maintence
1508: -- All data is now in the temporary table FII_COMPANY_HIER_GT,
1509: -- we need to maintain the permanent table FII_COMPANY_HIERARCHIES
1510: -- by diffing the 2 tables.
1511: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1512:
1513: DELETE FROM FII_COMPANY_HIERARCHIES
1509: -- we need to maintain the permanent table FII_COMPANY_HIERARCHIES
1510: -- by diffing the 2 tables.
1511: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1512:
1513: DELETE FROM FII_COMPANY_HIERARCHIES
1514: WHERE
1515: (parent_level, parent_company_id, next_level,
1516: next_level_company_id,
1517: next_level_is_leaf_flag, is_leaf_flag, child_level,
1529: child_flex_value_set_id, NVL(next_level_company_sort_order, -92883), LEVEL2_COMPANY_ID
1530: , LEVEL3_COMPANY_ID
1531: , LEVEL4_COMPANY_ID
1532: , LEVEL5_COMPANY_ID
1533: FROM FII_COMPANY_HIERARCHIES
1534: MINUS
1535: SELECT parent_level, parent_company_id,
1536: next_level, next_level_company_id,
1537: next_level_is_leaf_flag, is_leaf_flag,
1544: FROM fii_company_hier_gt);
1545:
1546:
1547: IF (FIIDIM_Debug) THEN
1548: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_COMPANY_HIERARCHIES');
1549: END IF;
1550:
1551: Insert into FII_COMPANY_HIERARCHIES (
1552: parent_level,
1547: IF (FIIDIM_Debug) THEN
1548: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_COMPANY_HIERARCHIES');
1549: END IF;
1550:
1551: Insert into FII_COMPANY_HIERARCHIES (
1552: parent_level,
1553: parent_company_id,
1554: next_level,
1555: next_level_company_id,
1613: FII_USER_ID,
1614: SYSDATE,
1615: FII_USER_ID,
1616: FII_LOGIN_ID
1617: FROM FII_COMPANY_HIERARCHIES);
1618:
1619: -- This will be in RSG data
1620: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1621: FND_STATS.gather_table_stats
1616: FII_LOGIN_ID
1617: FROM FII_COMPANY_HIERARCHIES);
1618:
1619: -- This will be in RSG data
1620: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1621: FND_STATS.gather_table_stats
1622: (ownname => g_schema_name,
1623: tabname => 'FII_COMPANY_HIERARCHIES');
1624:
1619: -- This will be in RSG data
1620: g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
1621: FND_STATS.gather_table_stats
1622: (ownname => g_schema_name,
1623: tabname => 'FII_COMPANY_HIERARCHIES');
1624:
1625: -- Bug 4200473. Not to analyze MLOG in incremental run.
1626: -- As per performance teams suggestions.
1627:
1630: -- (ownname => g_schema_name,
1631: -- tabname => 'MLOG$_FII_COMPANY_HIERARCH');
1632:
1633: IF (FIIDIM_Debug) THEN
1634: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
1635: END IF;
1636:
1637:
1638: --=============================================================--