[Home] [Help]
507:
508:
509: -- **************************************************************************
510: -- This procedure will check for child value multiple assignments
511: -- to different parents within FII_LOB_HIER_GT (the TMP hierarchy table)
512:
513: PROCEDURE Detect_Diamond_Shape IS
514:
515: --The first cursor is to find all flex_value_id which has multiple parents;
516: --we look at records such as (P1,A,A) and (P2,A,A)
517: Cursor Dup_Assg_Cur IS
518: SELECT count(parent_lob_id) parents,
519: child_lob_id flex_value_id
520: FROM FII_LOB_HIER_GT
521: WHERE next_level_lob_id = child_lob_id
522: AND parent_level = next_level - 1
523: GROUP BY child_lob_id
524: HAVING count(parent_lob_id) > 1;
529: SELECT parent_lob_id,
530: parent_flex_value_set_id,
531: child_lob_id,
532: child_flex_value_set_id
533: FROM FII_LOB_HIER_GT
534: WHERE child_lob_id = p_child_value_id
535: AND next_level_lob_id = child_lob_id
536: AND parent_level = next_level - 1;
537:
661: FOR direct_children_rec IN direct_children_csr(p_vset_id, p_root_node)
662: LOOP
663:
664: /* Inserting record with all parents */
665: INSERT INTO fii_lob_hier_gt (
666: parent_level,
667: parent_lob_id,
668: child_lob_id,
669: next_level,
731: FII_MESSAGE.Func_Ent(func_name => 'FII_LOB_MAINTAIN_PKG.'||
732: 'Flatten_LOB_Dim_Hier');
733: END IF;
734:
735: g_phase := 'Truncate table FII_LOB_HIER_GT';
736: FII_UTIL.truncate_table ('FII_LOB_HIER_GT', 'FII', g_retcode);
737:
738: -----------------------------------------------------------------
739:
732: 'Flatten_LOB_Dim_Hier');
733: END IF;
734:
735: g_phase := 'Truncate table FII_LOB_HIER_GT';
736: FII_UTIL.truncate_table ('FII_LOB_HIER_GT', 'FII', g_retcode);
737:
738: -----------------------------------------------------------------
739:
740: LOBDIM_parent_node := p_root_node;
757: INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
758:
759: g_phase := 'insert all self nodes';
760:
761: insert into fii_lob_hier_gt (
762: parent_level,
763: parent_lob_id,
764: next_level,
765: next_level_lob_id,
782: 'N'
783: from (select distinct child_lob_id,
784: child_level,
785: child_flex_value_set_id
786: from fii_lob_hier_gt);
787:
788: IF (FIIDIM_Debug) THEN
789: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
790: END IF;
785: child_flex_value_set_id
786: from fii_lob_hier_gt);
787:
788: IF (FIIDIM_Debug) THEN
789: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
790: END IF;
791:
792: g_phase := 'Insert self node for the top node';
793:
790: END IF;
791:
792: g_phase := 'Insert self node for the top node';
793:
794: INSERT INTO fii_LOB_hier_gt
795: (parent_level,
796: parent_lob_id,
797: next_level,
798: next_level_lob_id,
814: 'N',
815: 'N');
816:
817: IF (FIIDIM_Debug) THEN
818: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_LOB_hier_gt');
819: END IF;
820:
821: -- Insert a dummy super top node (-999) to the hierarchy table
822: -- (the dummy value set id is -998)
821: -- Insert a dummy super top node (-999) to the hierarchy table
822: -- (the dummy value set id is -998)
823: g_phase := 'Insert a dummy top node (-999) to the hierarchy table';
824:
825: INSERT INTO fii_lob_hier_gt
826: (parent_level,
827: parent_lob_id,
828: next_level,
829: next_level_lob_id,
843: child_flex_value_set_id,
844: -998,
845: 'N',
846: 'N'
847: FROM fii_lob_hier_gt
848: WHERE next_level_lob_id = parent_lob_id
849: AND next_level_lob_id = child_lob_id;
850:
851: IF (FIIDIM_Debug) THEN
848: WHERE next_level_lob_id = parent_lob_id
849: AND next_level_lob_id = child_lob_id;
850:
851: IF (FIIDIM_Debug) THEN
852: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
853: END IF;
854:
855: --Insert the UNASSIGNED to the hierarchy table.
856: --Use G_TOP_NODE_ID (rather than -999, see bug 3541141) as the parent
858: g_phase := 'Insert the UNASSIGNED to the hierarchy table';
859:
860: --bug 3541141: G_TOP_NODE_ID is the parent (to replace -999)
861: --First one is (G_TOP_NODE_ID, UNASSIGNED, UNASSIGNED)
862: INSERT INTO fii_lob_hier_gt
863: (parent_level,
864: parent_lob_id,
865: next_level,
866: next_level_lob_id,
882: 'N',
883: 'N');
884:
885: IF (FIIDIM_Debug) THEN
886: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
887: END IF;
888:
889: g_phase := 'Insert self node for UNASSIGNED to the hierarchy table';
890:
889: g_phase := 'Insert self node for UNASSIGNED to the hierarchy table';
890:
891: -- Another one is (UNASSIGNED, UNASSIGNED, UNASSIGNED)
892: -- bug 3541141: level becomes 2
893: INSERT INTO fii_lob_hier_gt
894: (parent_level,
895: parent_lob_id,
896: next_level,
897: next_level_lob_id,
913: 'N',
914: 'N');
915:
916: IF (FIIDIM_Debug) THEN
917: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
918: END IF;
919:
920: -- Added record (-999, TOP, UNASSIGNED) to the hierarchy
921: -- after the fix for bug 3541141
921: -- after the fix for bug 3541141
922: g_phase := 'Insert (-999, TOP, UNASSIGNED) to the hierarchy table';
923:
924: -- Another one is (-999, TOP, UNASSIGNED)
925: INSERT INTO fii_lob_hier_gt
926: (parent_level,
927: parent_lob_id,
928: next_level,
929: next_level_lob_id,
945: 'N',
946: 'N');
947:
948: IF (FIIDIM_Debug) THEN
949: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_lob_hier_gt');
950: END IF;
951:
952: --====================================================================
953: --Before we proceed to populate the final hierarchy table, we should
955: --If so, we will report the problem, and error out the program
956:
957: -- The following block checks for child value multiple assignments
958: -- to different parents within the value sets
959: -- We use (just created) TMP table FII_LOB_HIER_GT for this purpose
960: g_phase := 'Call Detect_Diamond_Shape';
961:
962: Detect_Diamond_Shape;
963:
971: -------------------------------------------------------
972: --Currently , there is no need to update this column for
973: --the full hierarchy since it's not used anywhere
974: -------------------------------------------------------
975: --Update fii_lob_hier_gt tab1
976: -- Set next_level_is_leaf = 'Y'
977: -- Where tab1.next_level_lob_id = tab1.child_lob_id
978: -- AND 1 = (select count(*)
979: -- from fii_lob_hier_gt tab2
975: --Update fii_lob_hier_gt tab1
976: -- Set next_level_is_leaf = 'Y'
977: -- Where tab1.next_level_lob_id = tab1.child_lob_id
978: -- AND 1 = (select count(*)
979: -- from fii_lob_hier_gt tab2
980: -- where tab2.parent_lob_id = tab1.next_level_lob_id);
981:
982: --Bug 3742786: Follow the performance enhancement in FC code.
983: --
980: -- where tab2.parent_lob_id = tab1.next_level_lob_id);
981:
982: --Bug 3742786: Follow the performance enhancement in FC code.
983: --
984: --Update fii_lob_hier_gt tab1
985: -- Set next_level_is_leaf = 'Y'
986: -- Where tab1.next_level_lob_id = tab1.child_lob_id
987: -- and tab1.next_level_lob_id IN (
988: -- select /*+ ordered */ tab3.next_level_lob_id
985: -- Set next_level_is_leaf = 'Y'
986: -- Where tab1.next_level_lob_id = tab1.child_lob_id
987: -- and tab1.next_level_lob_id IN (
988: -- select /*+ ordered */ tab3.next_level_lob_id
989: -- from fii_lob_hier_gt tab3,
990: -- fii_lob_hier_gt tab2
991: -- where tab2.parent_lob_id = tab3.parent_lob_id
992: -- and tab3.parent_lob_id = tab3.child_lob_id
993: -- group by tab3.next_level_lob_id
986: -- Where tab1.next_level_lob_id = tab1.child_lob_id
987: -- and tab1.next_level_lob_id IN (
988: -- select /*+ ordered */ tab3.next_level_lob_id
989: -- from fii_lob_hier_gt tab3,
990: -- fii_lob_hier_gt tab2
991: -- where tab2.parent_lob_id = tab3.parent_lob_id
992: -- and tab3.parent_lob_id = tab3.child_lob_id
993: -- group by tab3.next_level_lob_id
994: -- having count(*) = 1);
1000: -------------------------------------------------------
1001: --Currently , there is no need to update this column for
1002: --the full hierarchy since it's not used anywhere
1003: -------------------------------------------------------
1004: --Update fii_lob_hier_gt
1005: -- Set is_leaf_flag = 'Y'
1006: -- Where parent_lob_id = next_level_lob_id
1007: -- and next_level_lob_id = child_lob_id
1008: -- and next_level_is_leaf = 'Y';
1039: END Flatten_LOB_Dim_Hier;
1040:
1041:
1042: -- **************************************************************************
1043: -- Populate column next_level_lob_sort_order for pruned hierarchy FII_LOB_HIER_GT
1044: -- by looking at column HIERARCHY_LEVEL of FND_FLEX_VALUES for the master value set.
1045: -- This is for Oracle IT only currently.
1046:
1047: PROCEDURE Get_Sort_Order IS
1051: IF (FIIDIM_Debug) THEN
1052: FII_MESSAGE.Func_Ent(func_name => 'FII_LOB_MAINTAIN_PKG.'||'Get_Sort_Order');
1053: END IF;
1054:
1055: g_phase := 'Update next_level_lob_sort_order for fii_lob_hier_gt ';
1056:
1057: update fii_lob_hier_gt h
1058: set h.next_level_lob_sort_order =
1059: (select decode(TRANSLATE(HIERARCHY_LEVEL, 'A0123456789', 'A'), NULL,
1053: END IF;
1054:
1055: g_phase := 'Update next_level_lob_sort_order for fii_lob_hier_gt ';
1056:
1057: update fii_lob_hier_gt h
1058: set h.next_level_lob_sort_order =
1059: (select decode(TRANSLATE(HIERARCHY_LEVEL, 'A0123456789', 'A'), NULL,
1060: to_number(HIERARCHY_LEVEL), to_number(NULL))
1061: from fnd_flex_values ffv
1062: where ffv.flex_value_set_id = G_MASTER_VALUE_SET_ID
1063: and ffv.flex_value_id = h.next_level_lob_id);
1064:
1065: IF (FIIDIM_Debug) THEN
1066: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_lob_hier_gt');
1067: END IF;
1068:
1069: IF (FIIDIM_Debug) THEN
1070: FII_MESSAGE.Func_Succ(func_name=>'FII_LOB_MAINTAIN_PKG.'||'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:
1090: Begin
1093: FII_MESSAGE.Func_Ent(func_name => 'FII_LOB_MAINTAIN_PKG.'||
1094: 'Get_Pruned_LOB_GT');
1095: END IF;
1096:
1097: --Delete from FII_LOB_HIER_GT for child value set not equal to
1098: --the master value set and not equal to the UNASSIGNED value set.
1099: g_phase := 'Delete FII_LOB_HIER_GT #1';
1100:
1101: Delete from FII_LOB_HIER_GT
1095: END IF;
1096:
1097: --Delete from FII_LOB_HIER_GT for child value set not equal to
1098: --the master value set and not equal to the UNASSIGNED value set.
1099: g_phase := 'Delete FII_LOB_HIER_GT #1';
1100:
1101: Delete from FII_LOB_HIER_GT
1102: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1103: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1097: --Delete from FII_LOB_HIER_GT for child value set not equal to
1098: --the master value set and not equal to the UNASSIGNED value set.
1099: g_phase := 'Delete FII_LOB_HIER_GT #1';
1100:
1101: Delete from FII_LOB_HIER_GT
1102: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1103: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1104:
1105: IF (FIIDIM_Debug) THEN
1102: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1103: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1104:
1105: IF (FIIDIM_Debug) THEN
1106: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_LOB_HIER_GT');
1107: END IF;
1108:
1109: -- Bug 4299543. Leaf nodes will always be included in the pruned hierarchy from
1110: -- Expense Analysis onwards.
1109: -- Bug 4299543. Leaf nodes will always be included in the pruned hierarchy from
1110: -- Expense Analysis onwards.
1111:
1112: --Finally, update the columns next_level_is_leaf, is_leaf_flag again
1113: --for the latest FII_LOB_HIER_GT
1114: g_phase := 'Update next_level_is_leaf';
1115:
1116: --Update the column next_level_is_leaf
1117: --We look at those records (P,A,A) in which A is a leaf value
1114: g_phase := 'Update next_level_is_leaf';
1115:
1116: --Update the column next_level_is_leaf
1117: --We look at those records (P,A,A) in which A is a leaf value
1118: --Update fii_lob_hier_gt tab1
1119: -- Set next_level_is_leaf = 'Y'
1120: -- Where tab1.next_level_lob_id = tab1.child_lob_id
1121: -- AND 1 = (select count(*)
1122: -- from fii_lob_hier_gt tab2
1118: --Update fii_lob_hier_gt tab1
1119: -- Set next_level_is_leaf = 'Y'
1120: -- Where tab1.next_level_lob_id = tab1.child_lob_id
1121: -- AND 1 = (select count(*)
1122: -- from fii_lob_hier_gt tab2
1123: -- where tab2.parent_lob_id = tab1.next_level_lob_id);
1124:
1125: --Bug 3742786: Follow the performance enhancement in FC code.
1126: --
1123: -- where tab2.parent_lob_id = tab1.next_level_lob_id);
1124:
1125: --Bug 3742786: Follow the performance enhancement in FC code.
1126: --
1127: Update fii_lob_hier_gt tab1
1128: Set next_level_is_leaf = 'Y'
1129: Where tab1.next_level_lob_id = tab1.child_lob_id
1130: and tab1.next_level_lob_id IN (
1131: select /*+ ordered */ tab3.next_level_lob_id
1128: Set next_level_is_leaf = 'Y'
1129: Where tab1.next_level_lob_id = tab1.child_lob_id
1130: and tab1.next_level_lob_id IN (
1131: select /*+ ordered */ tab3.next_level_lob_id
1132: from fii_lob_hier_gt tab3,
1133: fii_lob_hier_gt tab2
1134: where tab2.parent_lob_id = tab3.parent_lob_id
1135: and tab3.parent_lob_id = tab3.child_lob_id
1136: group by tab3.next_level_lob_id
1129: Where tab1.next_level_lob_id = tab1.child_lob_id
1130: and tab1.next_level_lob_id IN (
1131: select /*+ ordered */ tab3.next_level_lob_id
1132: from fii_lob_hier_gt tab3,
1133: fii_lob_hier_gt tab2
1134: where tab2.parent_lob_id = tab3.parent_lob_id
1135: and tab3.parent_lob_id = tab3.child_lob_id
1136: group by tab3.next_level_lob_id
1137: having count(*) = 1);
1136: group by tab3.next_level_lob_id
1137: having count(*) = 1);
1138:
1139: IF (FIIDIM_Debug) THEN
1140: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_lob_hier_gt');
1141: END IF;
1142:
1143: g_phase := 'Update is_leaf_flag';
1144:
1143: g_phase := 'Update is_leaf_flag';
1144:
1145: --Update the column is_leaf_flag
1146: --We look at all records (A,A,A) in which A is a leaf value
1147: Update fii_lob_hier_gt
1148: Set is_leaf_flag = 'Y'
1149: Where parent_lob_id = next_level_lob_id
1150: and next_level_lob_id = child_lob_id
1151: and next_level_is_leaf = 'Y';
1150: and next_level_lob_id = child_lob_id
1151: and next_level_is_leaf = 'Y';
1152:
1153: IF (FIIDIM_Debug) THEN
1154: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_lob_hier_gt');
1155: END IF;
1156:
1157: --------------------------------------------------------------
1158: --Populate column next_level_lob_sort_order (bug 3608355)
1363: FII_USER_ID,
1364: SYSDATE,
1365: FII_USER_ID,
1366: FII_LOGIN_ID
1367: FROM FII_LOB_HIER_GT;
1368:
1369: IF (FIIDIM_Debug) THEN
1370: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_LOB_HIERS');
1371: END IF;
1380: tabname => 'FII_FULL_LOB_HIERS');
1381:
1382: --==============================================================--
1383:
1384: --Delete/Update FII_LOB_HIER_GT for pruned hierarchy table
1385: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1386:
1387: Get_Pruned_LOB_GT;
1388:
1381:
1382: --==============================================================--
1383:
1384: --Delete/Update FII_LOB_HIER_GT for pruned hierarchy table
1385: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1386:
1387: Get_Pruned_LOB_GT;
1388:
1389: --Copy FII_LOB_HIER_GT to the final (pruned) dimension table
1385: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1386:
1387: Get_Pruned_LOB_GT;
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:
1424: FII_USER_ID,
1425: SYSDATE,
1426: FII_USER_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;
1541:
1542: Flatten_LOB_Dim_Hier (G_MASTER_VALUE_SET_ID, G_TOP_NODE_VALUE);
1543:
1544: -- Incremental Dimension Maintence
1545: -- All data is now in the temporary table FII_LOB_HIER_GT,
1546: -- we need to maintain the permanent table FII_FULL_LOB_HIERS
1547: -- by diffing the 2 tables.
1548: -- The maintenance is done by 2 statements, one INSERT and one DELETE.
1549: g_phase := 'Copy TMP hierarchy table to the final full dimension table';
1566: MINUS
1567: SELECT parent_level, parent_lob_id, next_level, next_level_lob_id,
1568: next_level_is_leaf, is_leaf_flag, child_level, child_lob_id,
1569: parent_flex_value_set_id, child_flex_value_set_id
1570: FROM FII_LOB_HIER_GT);
1571:
1572: IF (FIIDIM_Debug) THEN
1573: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_FULL_LOB_HIERS');
1574: END IF;
1609: FII_USER_ID,
1610: SYSDATE,
1611: FII_USER_ID,
1612: FII_LOGIN_ID
1613: FROM FII_LOB_HIER_GT
1614: MINUS
1615: SELECT parent_level,
1616: parent_lob_id,
1617: next_level,
1643: tabname => 'FII_FULL_LOB_HIERS');
1644:
1645: --==============================================================--
1646:
1647: --Delete/Update FII_LOB_HIER_GT for pruned hierarchy table
1648: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1649:
1650: Get_Pruned_LOB_GT;
1651:
1644:
1645: --==============================================================--
1646:
1647: --Delete/Update FII_LOB_HIER_GT for pruned hierarchy table
1648: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1649:
1650: Get_Pruned_LOB_GT;
1651:
1652: --Copy FII_LOB_HIER_GT to the final (pruned) dimension table
1648: g_phase := 'Delete/Update FII_LOB_HIER_GT for pruned hierarchy table';
1649:
1650: Get_Pruned_LOB_GT;
1651:
1652: --Copy FII_LOB_HIER_GT to the final (pruned) dimension table
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,
1652: --Copy FII_LOB_HIER_GT to the final (pruned) dimension table
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:
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;
1717: FII_USER_ID,
1718: SYSDATE,
1719: FII_USER_ID,
1720: FII_LOGIN_ID
1721: FROM FII_LOB_HIER_GT
1722: MINUS
1723: SELECT parent_level,
1724: parent_lob_id,
1725: next_level,