[Home] [Help]
478:
479:
480: -- **************************************************************************
481: -- This procedure will check for child value multiple assignments
482: -- to different parents within FII_COMPANY_HIER_GT (the TMP hierarchy table)
483:
484: PROCEDURE Detect_Diamond_Shape IS
485:
486: --The first cursor is to find all flex_value_id which has multiple parents;
487: --we look at records such as (P1,A,A) and (P2,A,A)
488: Cursor Dup_Assg_Cur IS
489: SELECT count(parent_company_id) parents,
490: child_company_id flex_value_id
491: FROM FII_company_HIER_GT
492: WHERE next_level_company_id = child_company_id
493: AND parent_level = next_level - 1
494: GROUP BY child_company_id
495: HAVING count(parent_company_id) > 1;
500: SELECT parent_company_id,
501: parent_flex_value_set_id,
502: child_company_id,
503: child_flex_value_set_id
504: FROM FII_company_HIER_GT
505: WHERE child_company_id = p_child_value_id
506: AND next_level_company_id = child_company_id
507: AND parent_level = next_level - 1;
508:
636: FOR direct_children_rec IN direct_children_csr(p_vset_id, p_root_node)
637: LOOP
638:
639: /* Inserting record with all parents */
640: INSERT INTO fii_company_hier_gt (
641: parent_level,
642: parent_company_id,
643: child_company_id,
644: next_level,
702: CURSOR MAIN_CSR is
703: SELECT parent_level, parent_company_id, next_level, next_level_company_id,
704: child_level, child_company_id, child_flex_value_set_id,
705: parent_flex_value_set_id
706: FROM fii_company_hier_gt
707: ORDER BY parent_level, child_level;
708:
709: l_flex_value VARCHAR2(150);
710: p_parent_id NUMBER(15);
714: IF (FIIDIM_Debug) THEN
715: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
716: 'Flatten_CO_Dim_Hier');
717: END IF;
718: g_phase := 'Truncate table FII_COMPANY_HIER_GT';
719: FII_UTIL.truncate_table ('FII_COMPANY_HIER_GT', 'FII', g_retcode);
720:
721: -----------------------------------------------------------------
722:
715: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
716: 'Flatten_CO_Dim_Hier');
717: END IF;
718: g_phase := 'Truncate table FII_COMPANY_HIER_GT';
719: FII_UTIL.truncate_table ('FII_COMPANY_HIER_GT', 'FII', g_retcode);
720:
721: -----------------------------------------------------------------
722:
723: CODIM_parent_node := p_root_node;
738: g_phase := 'insert top node self row and invoke Ins_Imm_Child_nodes';
739:
740: INSERT_IMM_CHILD_NODES (p_vset_id, p_root_node);
741:
742: insert into fii_COMPANY_hier_gt (
743: parent_level,
744: parent_company_id,
745: next_level,
746: next_level_company_id,
760: child_flex_value_set_id,
761: child_flex_value_set_id,
762: 'N',
763: 'N'
764: from (select distinct child_company_id,child_level,child_flex_value_set_id from fii_company_hier_gt);
765:
766: IF (FIIDIM_Debug) THEN
767: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
768: END IF;
763: 'N'
764: from (select distinct child_company_id,child_level,child_flex_value_set_id from fii_company_hier_gt);
765:
766: IF (FIIDIM_Debug) THEN
767: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
768: END IF;
769:
770: INSERT INTO fii_COMPANY_hier_gt
771: (parent_level,
766: IF (FIIDIM_Debug) THEN
767: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
768: END IF;
769:
770: INSERT INTO fii_COMPANY_hier_gt
771: (parent_level,
772: parent_company_id,
773: next_level,
774: next_level_company_id,
790: 'N',
791: 'N');
792:
793: IF (FIIDIM_Debug) THEN
794: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
795: END IF;
796:
797: --Insert the UNASSIGNED to the hierarchy table.
798: --Use G_TOP_NODE_ID as the parent
798: --Use G_TOP_NODE_ID as the parent
799: g_phase := 'Insert the UNASSIGNED to the hierarchy table';
800:
801: -- First one is (G_TOP_NODE_ID, UNASSIGNED, UNASSIGNED)
802: INSERT INTO fii_COMPANY_hier_gt
803: (parent_level,
804: parent_company_id,
805: next_level,
806: next_level_company_id,
822: 'N',
823: 'N');
824:
825: IF (FIIDIM_Debug) THEN
826: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
827: END IF;
828:
829:
830: -- Another one is (UNASSIGNED, UNASSIGNED, UNASSIGNED)
827: END IF;
828:
829:
830: -- Another one is (UNASSIGNED, UNASSIGNED, UNASSIGNED)
831: INSERT INTO fii_COMPANY_hier_gt
832: (parent_level,
833: parent_company_id,
834: next_level,
835: next_level_company_id,
851: 'N',
852: 'N');
853:
854: IF (FIIDIM_Debug) THEN
855: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
856: END IF;
857:
858: -- Insert a dummy super top node (-999) to the hierarchy table
859: -- (the dummy value set id is -998)
858: -- Insert a dummy super top node (-999) to the hierarchy table
859: -- (the dummy value set id is -998)
860: g_phase := 'Insert a dummy top node (-999) to the hierarchy table';
861:
862: INSERT INTO fii_COMPANY_hier_gt
863: (parent_level,
864: parent_company_id,
865: next_level,
866: next_level_company_id,
880: child_flex_value_set_id,
881: -998,
882: 'N',
883: 'N'
884: FROM fii_COMPANY_hier_gt
885: WHERE child_company_id = parent_company_id;
886:
887: IF (FIIDIM_Debug) THEN
888: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
884: FROM fii_COMPANY_hier_gt
885: WHERE child_company_id = parent_company_id;
886:
887: IF (FIIDIM_Debug) THEN
888: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_hier_gt');
889: END IF;
890:
891:
892: --Call FND_STATS to collect statistics after populating the table
902: --If so, we will report the problem, and error out the program
903:
904: -- The following block checks for child value multiple assignments
905: -- to different parents within the value sets
906: -- We use (just created) TMP table FII_company_HIER_GT for this purpose
907: g_phase := 'Call Detect_Diamond_Shape';
908:
909: Detect_Diamond_Shape;
910:
951: , child_company_id cid
952: , child_level clv
953: , child_flex_value_set_id cvs
954: , is_leaf_flag clf
955: from fii_company_hier_gt
956: where parent_level + 1 = child_level
957: --and child_flex_value_set_id = G_MASTER_VALUE_SET_ID
958: union all
959: select NULL, -999, 0, -998, 'N'
989: ELSE
990: n_top := p_top + 1;
991: END IF;
992:
993: update fii_company_hier_gt
994: set LEVEL2_COMPANY_ID = r_stack( least( p_top + 2, c_top+1 ) ).cid
995: , LEVEL3_COMPANY_ID = r_stack( least( p_top + 3, c_top+1 ) ).cid
996: , LEVEL4_COMPANY_ID = r_stack( least( p_top + 4, c_top+1 ) ).cid
997: , LEVEL5_COMPANY_ID = r_stack( least( p_top + 5, c_top+1 ) ).cid
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:
1014: Begin
1016: IF (FIIDIM_Debug) THEN
1017: FII_MESSAGE.Func_Ent(func_name => 'FII_COM_MAINTAIN_PKG.'||
1018: 'Get_Pruned_CO_GT');
1019: END IF;
1020: --Delete from FII_company_HIER_GT for child value set not equal to
1021: --the master value set and not equal to the UNASSIGNED value set.
1022: g_phase := 'Delete FII_company_HIER_GT #1';
1023:
1024: Delete from FII_COMPANY_HIER_GT
1018: 'Get_Pruned_CO_GT');
1019: END IF;
1020: --Delete from FII_company_HIER_GT for child value set not equal to
1021: --the master value set and not equal to the UNASSIGNED value set.
1022: g_phase := 'Delete FII_company_HIER_GT #1';
1023:
1024: Delete from FII_COMPANY_HIER_GT
1025: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1026: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1020: --Delete from FII_company_HIER_GT for child value set not equal to
1021: --the master value set and not equal to the UNASSIGNED value set.
1022: g_phase := 'Delete FII_company_HIER_GT #1';
1023:
1024: Delete from FII_COMPANY_HIER_GT
1025: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1026: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1027:
1028: IF (FIIDIM_Debug) THEN
1025: Where child_flex_value_set_id <> G_MASTER_VALUE_SET_ID
1026: And child_flex_value_set_id <> G_FII_INT_VALUE_SET_ID;
1027:
1028: IF (FIIDIM_Debug) THEN
1029: FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows form FII_COMPANY_HIER_GT');
1030: END IF;
1031:
1032: -- Calling procedure to update data in the new columns added for SBA content
1033: Get_level_populated;
1032: -- Calling procedure to update data in the new columns added for SBA content
1033: Get_level_populated;
1034:
1035: --Finally, update the columns next_level_is_leaf_flag, is_leaf_flag again
1036: --for the latest FII_company_HIER_GT
1037: g_phase := 'Update next_level_is_leaf_flag, is_leaf_flag';
1038:
1039: --Update the column next_level_is_leaf_flag
1040: --We look at those records (P,A,A) in which A is a leaf value
1037: g_phase := 'Update next_level_is_leaf_flag, is_leaf_flag';
1038:
1039: --Update the column next_level_is_leaf_flag
1040: --We look at those records (P,A,A) in which A is a leaf value
1041: Update fii_company_hier_gt tab1
1042: Set next_level_is_leaf_flag = 'Y'
1043: Where tab1.next_level_company_id = tab1.child_company_id
1044: and tab1.next_level_company_id IN (
1045: select /*+ ordered */ tab3.next_level_company_id
1042: Set next_level_is_leaf_flag = 'Y'
1043: Where tab1.next_level_company_id = tab1.child_company_id
1044: and tab1.next_level_company_id IN (
1045: select /*+ ordered */ tab3.next_level_company_id
1046: from fii_company_hier_gt tab3,
1047: fii_company_hier_gt tab2
1048: where tab2.parent_company_id = tab3.parent_company_id
1049: and tab3.parent_company_id = tab3.child_company_id
1050: group by tab3.next_level_company_id
1043: Where tab1.next_level_company_id = tab1.child_company_id
1044: and tab1.next_level_company_id IN (
1045: select /*+ ordered */ tab3.next_level_company_id
1046: from fii_company_hier_gt tab3,
1047: fii_company_hier_gt tab2
1048: where tab2.parent_company_id = tab3.parent_company_id
1049: and tab3.parent_company_id = tab3.child_company_id
1050: group by tab3.next_level_company_id
1051: having count(*) = 1);
1050: group by tab3.next_level_company_id
1051: having count(*) = 1);
1052:
1053: IF (FIIDIM_Debug) THEN
1054: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COMPANY_HIER_GT');
1055: END IF;
1056:
1057: --Update the column is_leaf_flag
1058: --We look at all records (A,A,A) in which A is a leaf value
1055: END IF;
1056:
1057: --Update the column is_leaf_flag
1058: --We look at all records (A,A,A) in which A is a leaf value
1059: Update fii_COMPANY_hier_gt
1060: Set is_leaf_flag = 'Y'
1061: Where parent_company_id = child_company_id
1062: and next_level_is_leaf_flag = 'Y';
1063:
1061: Where parent_company_id = child_company_id
1062: and next_level_is_leaf_flag = 'Y';
1063:
1064: IF (FIIDIM_Debug) THEN
1065: FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COMPANY_HIER_GT');
1066: END IF;
1067:
1068: IF (FIIDIM_Debug) THEN
1069: FII_MESSAGE.Func_Succ(func_name => 'FII_COM_MAINTAIN_PKG.'||
1276: FII_USER_ID,
1277: SYSDATE,
1278: FII_USER_ID,
1279: FII_LOGIN_ID
1280: FROM FII_COMPANY_HIER_GT;
1281:
1282: IF (FIIDIM_Debug) THEN
1283: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows in FII_FULL_COMPANY_HIERS');
1284: END IF;
1290: tabname => 'FII_FULL_COMPANY_HIERS');
1291:
1292: --==============================================================--
1293:
1294: --Delete/Update FII_company_HIER_GT for pruned hierarchy table
1295: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1296:
1297: Get_Pruned_CO_GT;
1298:
1291:
1292: --==============================================================--
1293:
1294: --Delete/Update FII_company_HIER_GT for pruned hierarchy table
1295: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1296:
1297: Get_Pruned_CO_GT;
1298:
1299: --Copy FII_company_HIER_GT to the final (pruned) dimension table
1295: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1296:
1297: Get_Pruned_CO_GT;
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:
1344: FII_USER_ID,
1345: SYSDATE,
1346: FII_USER_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;
1481: FII_USER_ID,
1482: SYSDATE,
1483: FII_USER_ID,
1484: FII_LOGIN_ID
1485: FROM FII_COMPANY_HIER_GT;
1486:
1487: IF (FIIDIM_Debug) THEN
1488: FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FULL_COMPANY_HIERS');
1489: END IF;
1495: tabname => 'FII_FULL_COMPANY_HIERS');
1496:
1497: --==============================================================--
1498:
1499: --Delete/Update FII_company_HIER_GT for pruned hierarchy table
1500: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1501:
1502: Get_Pruned_CO_GT;
1503:
1496:
1497: --==============================================================--
1498:
1499: --Delete/Update FII_company_HIER_GT for pruned hierarchy table
1500: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1501:
1502: Get_Pruned_CO_GT;
1503:
1504: --Copy FII_company_HIER_GT to the final (pruned) dimension table
1500: g_phase := 'Delete/Update FII_company_HIER_GT for pruned hierarchy table';
1501:
1502: Get_Pruned_CO_GT;
1503:
1504: --Copy FII_company_HIER_GT to the final (pruned) dimension table
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,
1504: --Copy FII_company_HIER_GT to the final (pruned) dimension table
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:
1540: child_flex_value_set_id, NVL(next_level_company_sort_order, -92883),LEVEL2_COMPANY_ID
1541: , LEVEL3_COMPANY_ID
1542: , LEVEL4_COMPANY_ID
1543: , LEVEL5_COMPANY_ID
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');
1590: FII_USER_ID,
1591: SYSDATE,
1592: FII_USER_ID,
1593: FII_LOGIN_ID
1594: FROM fii_company_hier_gt
1595: MINUS
1596: SELECT parent_level,
1597: parent_company_id,
1598: next_level,