[Home] [Help]
1081: END Get_Sort_Order;
1082:
1083:
1084: -- **************************************************************************
1085: -- Populate the pruned LOB hierarchy FII_LOB_HIERARCHIES by deleting from
1086: -- FII_LOB_HIER_GT (full version) the values from Local Value sets
1087:
1088: PROCEDURE Get_Pruned_LOB_GT IS
1089:
1193:
1194: g_phase := 'Truncate tables';
1195:
1196: FII_UTIL.truncate_table ('FII_FULL_LOB_HIERS', 'FII', g_retcode);
1197: FII_UTIL.truncate_table ('FII_LOB_HIERARCHIES', 'FII', g_retcode);
1198:
1199: g_phase := 'INSERT INTO FII_FULL_LOB_HIERS';
1200:
1201: INSERT INTO FII_FULL_LOB_HIERS
1234: IF (FIIDIM_Debug) THEN
1235: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_LOB_HIERS');
1236: END IF;
1237:
1238: g_phase := 'INSERT INTO FII_LOB_HIERARCHIES';
1239:
1240: INSERT INTO FII_LOB_HIERARCHIES
1241: (parent_level,
1242: parent_lob_id,
1236: END IF;
1237:
1238: g_phase := 'INSERT INTO FII_LOB_HIERARCHIES';
1239:
1240: INSERT INTO FII_LOB_HIERARCHIES
1241: (parent_level,
1242: parent_lob_id,
1243: next_level,
1244: next_level_lob_id,
1270: FII_USER_ID,
1271: FII_LOGIN_ID);
1272:
1273: IF (FIIDIM_Debug) THEN
1274: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_LOB_HIERARCHIES');
1275: END IF;
1276:
1277: commit;
1278:
1388:
1389: --Copy FII_LOB_HIER_GT to the final (pruned) dimension table
1390: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1391:
1392: FII_UTIL.truncate_table ('FII_LOB_HIERARCHIES', 'FII', g_retcode);
1393:
1394: INSERT /*+ APPEND */ INTO FII_LOB_HIERARCHIES (
1395: parent_level,
1396: parent_lob_id,
1390: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1391:
1392: FII_UTIL.truncate_table ('FII_LOB_HIERARCHIES', 'FII', g_retcode);
1393:
1394: INSERT /*+ APPEND */ INTO FII_LOB_HIERARCHIES (
1395: parent_level,
1396: parent_lob_id,
1397: next_level,
1398: next_level_lob_id,
1427: FII_LOGIN_ID
1428: FROM FII_LOB_HIER_GT;
1429:
1430: IF (FIIDIM_Debug) THEN
1431: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_LOB_HIERARCHIES');
1432: END IF;
1433:
1434: --Call FND_STATS to collect statistics after re-populating the tables.
1435: --Will seed this in RSG
1434: --Call FND_STATS to collect statistics after re-populating the tables.
1435: --Will seed this in RSG
1436: -- FND_STATS.gather_table_stats
1437: -- (ownname => g_schema_name,
1438: -- tabname => 'FII_LOB_HIERARCHIES');
1439:
1440: --================================================================--
1441:
1442: FND_CONCURRENT.Af_Commit;
1653: g_phase := 'Copy TMP hierarchy table to the final pruned dimension table';
1654:
1655: -- Incremental Dimension Maintence
1656: -- All data is now in the temporary table FII_LOB_HIER_GT,
1657: -- we need to maintain the permanent table FII_LOB_HIERARCHIES
1658: -- by diffing the 2 tables.
1659: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1660:
1661: g_phase := 'DELETE FROM FII_LOB_HIERARCHIES';
1657: -- we need to maintain the permanent table FII_LOB_HIERARCHIES
1658: -- by diffing the 2 tables.
1659: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1660:
1661: g_phase := 'DELETE FROM FII_LOB_HIERARCHIES';
1662:
1663: --use NVL to handle possible NULL column
1664: DELETE FROM FII_LOB_HIERARCHIES
1665: WHERE
1660:
1661: g_phase := 'DELETE FROM FII_LOB_HIERARCHIES';
1662:
1663: --use NVL to handle possible NULL column
1664: DELETE FROM FII_LOB_HIERARCHIES
1665: WHERE
1666: (parent_level, parent_lob_id, next_level, next_level_lob_id,
1667: next_level_is_leaf, is_leaf_flag, child_level, child_lob_id,
1668: parent_flex_value_set_id, child_flex_value_set_id,
1670: (SELECT parent_level, parent_lob_id, next_level, next_level_lob_id,
1671: next_level_is_leaf, is_leaf_flag, child_level, child_lob_id,
1672: parent_flex_value_set_id, child_flex_value_set_id,
1673: NVL(next_level_lob_sort_order, -92883)
1674: FROM FII_LOB_HIERARCHIES
1675: MINUS
1676: SELECT parent_level, parent_lob_id, next_level, next_level_lob_id,
1677: next_level_is_leaf, is_leaf_flag, child_level, child_lob_id,
1678: parent_flex_value_set_id, child_flex_value_set_id,
1679: NVL(next_level_lob_sort_order, -92883)
1680: FROM FII_LOB_HIER_GT);
1681:
1682: IF (FIIDIM_Debug) THEN
1683: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_LOB_HIERARCHIES');
1684: END IF;
1685:
1686: g_phase := 'Insert into FII_LOB_HIERARCHIES';
1687:
1682: IF (FIIDIM_Debug) THEN
1683: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_LOB_HIERARCHIES');
1684: END IF;
1685:
1686: g_phase := 'Insert into FII_LOB_HIERARCHIES';
1687:
1688: Insert into FII_LOB_HIERARCHIES (
1689: parent_level,
1690: parent_lob_id,
1684: END IF;
1685:
1686: g_phase := 'Insert into FII_LOB_HIERARCHIES';
1687:
1688: Insert into FII_LOB_HIERARCHIES (
1689: parent_level,
1690: parent_lob_id,
1691: next_level,
1692: next_level_lob_id,
1735: FII_USER_ID,
1736: SYSDATE,
1737: FII_USER_ID,
1738: FII_LOGIN_ID
1739: FROM FII_LOB_HIERARCHIES);
1740:
1741: IF (FIIDIM_Debug) THEN
1742: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_LOB_HIERARCHIES');
1743: END IF;
1738: FII_LOGIN_ID
1739: FROM FII_LOB_HIERARCHIES);
1740:
1741: IF (FIIDIM_Debug) THEN
1742: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_LOB_HIERARCHIES');
1743: END IF;
1744:
1745: --Call FND_STATS to collect statistics after re-populating the tables.
1746: --Will seed this in RSG
1745: --Call FND_STATS to collect statistics after re-populating the tables.
1746: --Will seed this in RSG
1747: -- FND_STATS.gather_table_stats
1748: -- (ownname => g_schema_name,
1749: -- tabname => 'FII_LOB_HIERARCHIES');
1750:
1751: --=============================================================--
1752:
1753: FND_CONCURRENT.Af_Commit;