APPS.FII_PMV_HELPER_TABLES_C SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 161

	  SELECT   DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
          INTO G_FC_DBI_ENABLED_FLAG, G_FC_TOP_NODE_ID
          FROM FII_FINANCIAL_DIMENSIONS
          WHERE DIMENSION_SHORT_NAME = 'GL_FII_FIN_ITEM';

Line: 196

	  SELECT   DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
          INTO G_CO_DBI_ENABLED_FLAG, G_CO_TOP_NODE_ID
          FROM FII_FINANCIAL_DIMENSIONS
          WHERE DIMENSION_SHORT_NAME = 'FII_COMPANIES';

Line: 235

	  SELECT   DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
          INTO G_CC_DBI_ENABLED_FLAG, G_CC_TOP_NODE_ID
          FROM FII_FINANCIAL_DIMENSIONS
          WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';

Line: 275

	  SELECT   DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
          INTO G_UDD1_DBI_ENABLED_FLAG, G_UDD1_TOP_NODE_ID
          FROM FII_FINANCIAL_DIMENSIONS
          WHERE DIMENSION_SHORT_NAME = 'FII_USER_DEFINED_1';

Line: 354

        INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)

        select
               NEXT_LEVEL_FIN_CAT_ID,
               subtree_freq,
               'GL_FII_FIN_ITEM'
        from FII_FIN_ITEM_LEAF_HIERS,
             (select h.PARENT_FIN_CAT_ID root_id,
                     count(*) subtree_freq
               from FII_FIN_ITEM_LEAF_HIERS h
               group by h.PARENT_FIN_CAT_ID) g
        where parent_fin_cat_id = g.root_id
        and (PARENT_FIN_CAT_ID <> NEXT_LEVEL_FIN_CAT_ID
         Or NEXT_LEVEL_FIN_CAT_ID = G_FC_TOP_NODE_ID )
        group by PARENT_FIN_CAT_ID,
                 NEXT_LEVEL_FIN_CAT_ID,
                 subtree_freq ;

Line: 376

	 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 380

	UPDATE FII_AGGRT_NODE_GT
	SET no_of_children = no_of_children + 1
	WHERE id = G_FC_TOP_NODE_ID ;

Line: 385

	 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');

Line: 392

         INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)
         VALUES(G_UNASSIGNED_ID, 1, 'GL_FII_FIN_ITEM');

Line: 399

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 406

        INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)

        select
               NEXT_LEVEL_COMPANY_ID,
               subtree_freq,
               'FII_COMPANIES'
        from fii_COMPANY_hierarchies,
             (select h.PARENT_COMPANY_ID root_id,
                     count(*) subtree_freq
               from fii_COMPANY_hierarchies h
               group by h.PARENT_COMPANY_ID) g
        where parent_COMPANY_id = g.root_id
        and PARENT_COMPANY_ID <> NEXT_LEVEL_COMPANY_ID
        group by PARENT_COMPANY_ID,
                 NEXT_LEVEL_COMPANY_ID,
                 subtree_freq ;

Line: 427

	 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 431

	 UPDATE FII_AGGRT_NODE_GT
	 SET no_of_children = no_of_children + 1
	 WHERE id = G_CO_TOP_NODE_ID ;

Line: 436

	 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');

Line: 442

         INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)
         VALUES(G_UNASSIGNED_ID, 1, 'FII_COMPANIES');

Line: 449

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 457

        INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)

        select
               NEXT_LEVEL_CC_ID,
               subtree_freq,
               'HRI_CL_ORGCC'
        from fii_COST_CTR_hierarchies,
             (select h.PARENT_CC_ID root_id,
                     count(*) subtree_freq
               from fii_COST_CTR_hierarchies h
               group by h.PARENT_CC_ID) g
        where parent_CC_id = g.root_id
        and PARENT_CC_ID <> NEXT_LEVEL_CC_ID
        group by PARENT_CC_ID,
                 NEXT_LEVEL_CC_ID,
                 subtree_freq ;

Line: 478

	 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 482

	 UPDATE FII_AGGRT_NODE_GT
	 SET no_of_children = no_of_children + 1
	 WHERE id = G_CC_TOP_NODE_ID ;

Line: 487

	 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');

Line: 495

         INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)
         VALUES(G_UNASSIGNED_ID, 1, 'HRI_CL_ORGCC');

Line: 502

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 510

       INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)

        select NEXT_LEVEL_VALUE_ID,
               subtree_freq,
               'FII_USER_DEFINED_1'
        from fii_UDD1_hierarchies,
             (select h.PARENT_VALUE_ID root_id,
                     count(*) subtree_freq
               from fii_UDD1_hierarchies h
               group by h.PARENT_VALUE_ID) g
        where parent_VALUE_id = g.root_id
        and PARENT_VALUE_ID <> NEXT_LEVEL_VALUE_ID
        group by PARENT_VALUE_ID,
                 NEXT_LEVEL_VALUE_ID,
                 subtree_freq ;

Line: 530

	 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 534

	 UPDATE FII_AGGRT_NODE_GT
	 SET no_of_children = no_of_children + 1
	 WHERE id = G_UDD1_TOP_NODE_ID ;

Line: 539

	 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');

Line: 547

         INSERT INTO FII_AGGRT_NODE_GT (
                            id,
                            no_of_children,
                            dim_short_name)
         VALUES(G_UNASSIGNED_ID, 1, 'FII_USER_DEFINED_1');

Line: 554

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');

Line: 583

         select max (no_of_children) no_of_children, dim_short_name
         from fii_aggrt_node_gt
         group by dim_short_name
         ORDER BY no_of_children;

Line: 634

   Procedure update_hierarchy(p_dim_short_name varchar2) IS
    l_max_level number;

Line: 639

    FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.update_hierarchy');

Line: 653

        select max(child_level) into l_max_level
        from fii_fin_cat_type_assgns, fii_fin_item_leaf_hiers
	where top_node_flag = 'Y'
	and fin_category_id = child_fin_Cat_id
	and parent_level = child_level
	and fin_cat_type_code = 'R';

Line: 665

      update fii_fin_item_leaf_hiers f
      set aggregate_next_level_flag = 'Y'
      where aggregate_next_level_flag <> 'Y'
      and (next_level in (1,2)
      or (next_level <= l_max_level + 1
          and f.next_level_fin_cat_id in (select fin_category_id
	                                  from fii_fin_cat_type_assgns
					  where fin_cat_type_code = 'R')));

Line: 675

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');

Line: 679

        select max(child_level) into l_max_level
        from fii_fin_cat_type_assgns, fii_fin_item_leaf_hiers
	where top_node_flag = 'Y'
	and fin_category_id = child_fin_Cat_id
	and parent_level = child_level
	and fin_cat_type_code = 'EXP';

Line: 687

      update fii_fin_item_leaf_hiers f
      set aggregate_next_level_flag = 'Y'
      where aggregate_next_level_flag <> 'Y'
      and (f.next_level <= l_max_level + 1
          and f.next_level_fin_cat_id in (select fin_category_id
	                                  from fii_fin_cat_type_assgns
					  where fin_cat_type_code = 'EXP'));

Line: 696

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');

Line: 700

      update fii_fc_pmv_agrt_nodes f
      set aggregated_flag = (select aggregate_next_level_flag
                                        from fii_fin_item_leaf_hiers
                                       where next_level_fin_cat_id = f.fin_category_id
				       and parent_level = next_level)
      where aggregated_flag <> (select aggregate_next_level_flag
                                        from fii_fin_item_leaf_hiers
                                       where next_level_fin_cat_id = f.fin_category_id
				       and parent_level = next_level);

Line: 711

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');

Line: 730

     update fii_cost_ctr_hierarchies f
     set aggregate_next_level_flag = 'Y'
      where aggregate_next_level_flag <> 'Y'
      and next_level in (1,2);

Line: 737

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');

Line: 741

      update fii_cc_pmv_agrt_nodes f
      set aggregated_flag = (select aggregate_next_level_flag
                                        from fii_cost_ctr_hierarchies
                                       where next_level_cc_id = f.cost_center_id
				       and parent_level = next_level)
      where aggregated_flag <> (select aggregate_next_level_flag
                                        from fii_cost_ctr_hierarchies
                                       where next_level_cc_id = f.cost_center_id
				       and parent_level = next_level);

Line: 752

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');

Line: 771

     update fii_company_hierarchies f
     set aggregate_next_level_flag = 'Y'
      where aggregate_next_level_flag <> 'Y'
      and next_level in (1,2);

Line: 777

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');

Line: 781

      update fii_com_pmv_agrt_nodes f
      set aggregated_flag = (select aggregate_next_level_flag
                                        from fii_company_hierarchies
                                       where next_level_company_id = f.company_id
				       and parent_level = next_level)
      where aggregated_flag <> (select aggregate_next_level_flag
                                        from fii_company_hierarchies
                                       where next_level_company_id = f.company_id
				       and parent_level = next_level);

Line: 792

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');

Line: 811

     update fii_udd1_hierarchies f
     set aggregate_next_level_flag = 'Y'
      where aggregate_next_level_flag <> 'Y'
      and next_level in (1,2);

Line: 817

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');

Line: 821

      update fii_udd1_pmv_agrt_nodes f
      set aggregated_flag = (select aggregate_next_level_flag
                                        from fii_udd1_hierarchies
                                       where next_level_value_id = f.udd1_value_id
				       and parent_level = next_level)
      where aggregated_flag <> (select aggregate_next_level_flag
                                        from fii_udd1_hierarchies
                                       where next_level_value_id = f.udd1_value_id
				       and parent_level = next_level);

Line: 832

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');

Line: 851

     FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.update_hierarchy');

Line: 854

   END update_hierarchy;

Line: 861

   Procedure Update_pruned_table (p_dim_short_name varchar2) IS
    l_max_agrt_level number;

Line: 866

       FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.Update_pruned_table');

Line: 872

     g_phase := 'In IF Update fii_fin_item_leaf_hiers and set the aggregate_next_level_flag';

Line: 873

      update FII_FIN_ITEM_LEAF_HIERS f
      set aggregate_next_level_flag = (select aggregated_flag
                                        from fii_fc_pmv_agrt_nodes
                                       where fin_category_id = f.next_level_fin_cat_id)
      where aggregate_next_level_flag <> (select aggregated_flag
                                           from fii_fc_pmv_agrt_nodes
                                          where fin_category_id = f.next_level_fin_cat_id);

Line: 882

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');

Line: 886

     g_phase := 'In ELSE Update FII_FIN_ITEM_LEAF_HIERS and set the aggregate_next_level_flag';

Line: 887

      update FII_FIN_ITEM_LEAF_HIERS f
      set aggregate_next_level_flag = (select aggregated_flag
                                       from fii_fc_pmv_agrt_nodes
                                       where fin_category_id = f.next_level_fin_cat_id);

Line: 893

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');

Line: 907

     g_phase := 'In IF Update fii_company_hierarchies and set the aggregate_next_level_flag';

Line: 908

      update FII_COMPANY_HIERARCHIES f
      set aggregate_next_level_flag = (select aggregated_flag
                                        from fii_com_pmv_agrt_nodes
                                       where company_id = f.next_level_company_id)
      where aggregate_next_level_flag <> (select aggregated_flag
                                           from fii_com_pmv_agrt_nodes
                                          where company_id = f.next_level_company_id);

Line: 917

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');

Line: 921

     g_phase := 'In ELSE Update fii_company_hierarchies and set the aggregate_next_level_flag';

Line: 922

      update fii_company_hierarchies f
     set aggregate_next_level_flag = (select aggregated_flag
                                       from  fii_com_pmv_agrt_nodes
                                      where company_id = f.next_level_company_id);

Line: 928

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');

Line: 933

    select max(next_level) into l_max_agrt_level
    from fii_company_hierarchies
    where aggregate_next_level_flag = 'Y';

Line: 941

     g_phase := 'In IF Update fii_cost_ctr_hierarchies and set the aggregate_next_level_flag';

Line: 942

     update fii_cost_ctr_hierarchies f
     set aggregate_next_level_flag = (select aggregated_flag
                                        from fii_cc_pmv_agrt_nodes
                                       where cost_center_id = f.next_level_cc_id)
      where aggregate_next_level_flag <> (select aggregated_flag
                                           from fii_cc_pmv_agrt_nodes
                                          where cost_center_id = f.next_level_cc_id);

Line: 951

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');

Line: 955

     g_phase := 'In ELSE Update fii_cost_ctr_hierarchies and set the aggregate_next_level_flag';

Line: 956

     update fii_cost_ctr_hierarchies f
     set aggregate_next_level_flag = (select aggregated_flag
                                     from  fii_cc_pmv_agrt_nodes
                                     where cost_center_id = f.next_level_cc_id);

Line: 962

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');

Line: 967

    select max(next_level) into l_max_agrt_level
    from fii_cost_ctr_hierarchies
    where aggregate_next_level_flag = 'Y';

Line: 975

     g_phase := 'In IF Update fii_udd1_hierarchies and set the aggregate_next_level_flag';

Line: 976

     update fii_udd1_hierarchies f
     set aggregate_next_level_flag = (select aggregated_flag
                                        from fii_udd1_pmv_agrt_nodes
                                       where UDD1_VALUE_ID = f.next_level_value_id)
      where aggregate_next_level_flag <> (select aggregated_flag
                                           from fii_udd1_pmv_agrt_nodes
                                          where UDD1_VALUE_ID = f.next_level_value_id);

Line: 985

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');

Line: 989

     g_phase := 'In ELSE Update fii_udd1_hierarchies and set the aggregate_next_level_flag';

Line: 990

     update fii_udd1_hierarchies f
     set aggregate_next_level_flag = (select aggregated_flag
                                      from fii_udd1_pmv_agrt_nodes
                                      where UDD1_VALUE_ID = f.next_level_value_id);

Line: 996

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');

Line: 1001

    select max(next_level) into l_max_agrt_level
    from fii_udd1_hierarchies
    where aggregate_next_level_flag = 'Y';

Line: 1011

     update_hierarchy(p_dim_short_name);

Line: 1016

      g_phase := 'Calling update_hierarchy';

Line: 1017

      update_hierarchy(p_dim_short_name);

Line: 1022

       FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.Update_pruned_table');

Line: 1028

        FII_UTIL.Write_Log ('Unexpected error when calling Update_pruned_table...');

Line: 1033

END Update_pruned_table;

Line: 1040

   Procedure update_viewby_flag (p_dim_short_name varchar2) IS
   BEGIN

     IF (FIIDIM_Debug) THEN
       FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.update_viewby_flag');

Line: 1050

     g_phase := 'Update FII_FC_PMV_AGRT_NODES for Financial Category';

Line: 1052

     UPDATE FII_FC_PMV_AGRT_NODES
     set for_viewby_flag = 'Y'
     WHERE fin_category_id in ( select next_level_fin_cat_id
 				from FII_FIN_ITEM_LEAF_HIERS f1
                               where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
                               or exists ( select aggregate_next_level_flag
                                             from FII_FIN_ITEM_LEAF_HIERS f2
                                            where f1.next_level_fin_cat_id =  f2.parent_fin_cat_id
                                              and f2.aggregate_next_level_flag = 'Y'
                                              and f2.parent_fin_cat_id <> f2.next_level_fin_cat_id));

Line: 1063

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_FC_PMV_AGRT_NODES');

Line: 1068

     g_phase := 'Update FII_COM_PMV_AGRT_NODES for Company';

Line: 1070

     UPDATE FII_COM_PMV_AGRT_NODES
     set for_viewby_flag = 'Y'
     WHERE company_id in ( select next_level_company_id
 					from FII_COMPANY_HIERARCHIES f1
                               where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
                               or exists ( select aggregate_next_level_flag
                                             from FII_COMPANY_HIERARCHIES f2
                                            where f1.next_level_company_id =  f2.parent_company_id
                                              and f2.aggregate_next_level_flag = 'Y'
                                              and f2.parent_company_id <> f2.next_level_company_id));

Line: 1082

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_PMV_AGRT_NODES');

Line: 1087

     g_phase := 'Update FII_CC_PMV_AGRT_NODES for Cost Center';

Line: 1089

     UPDATE FII_CC_PMV_AGRT_NODES
     set for_viewby_flag = 'Y'
     WHERE cost_center_id in ( select next_level_cc_id
 				from FII_COST_CTR_HIERARCHIES f1
                               where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
                               or exists ( select aggregate_next_level_flag
                                             from FII_COST_CTR_HIERARCHIES f2
                                            where f1.next_level_cc_id =  f2.parent_cc_id
                                              and f2.aggregate_next_level_flag = 'Y'
                                              and f2.parent_cc_id <> f2.next_level_cc_id));

Line: 1101

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_PMV_AGRT_NODES');

Line: 1105

     g_phase := 'Update FII_UDD1_PMV_AGRT_NODES for User Defined Dimension1';

Line: 1107

     UPDATE FII_UDD1_PMV_AGRT_NODES
     set for_viewby_flag = 'Y'
     WHERE udd1_value_id in ( select next_level_value_id
 					from FII_UDD1_HIERARCHIES f1
                               where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
                               or exists ( select aggregate_next_level_flag
                                             from FII_UDD1_HIERARCHIES f2
                                            where f1.next_level_value_id =  f2.parent_value_id
                                              and f2.aggregate_next_level_flag = 'Y'
                                              and f2.parent_value_id <> f2.next_level_value_id));

Line: 1119

	   FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_UDD1_PMV_AGRT_NODES');

Line: 1125

       FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.update_viewby_flag');

Line: 1131

        FII_UTIL.Write_Log ('Unexpected error when calling update_viewby_flag...');

Line: 1136

   END update_viewby_flag;

Line: 1162

         g_phase := 'Inserting into fii_fc_pmv_agrt_nodes for FC';

Line: 1163

         Insert into fii_fc_pmv_agrt_nodes(FIN_CATEGORY_ID,
	                                for_viewby_flag,
                                        aggregated_flag,
				        LAST_UPDATE_DATE,
					LAST_UPDATED_BY,
					CREATION_DATE,
					CREATED_BY ,
					LAST_UPDATE_LOGIN)
        (select next_level_fin_cat_id, 'Y','Y',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
         from FII_FIN_ITEM_LEAF_HIERS
         where parent_level= next_level
         ) ;

Line: 1182

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');

Line: 1186

         g_phase := 'Inserting into fii_com_pmv_agrt_nodes for Company';

Line: 1187

         Insert into fii_com_pmv_agrt_nodes(COMPANY_ID,
	                        for_viewby_flag,
                                aggregated_flag,
			        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY ,
				CREATION_DATE ,
				CREATED_BY  ,
				LAST_UPDATE_LOGIN)
         (select next_level_company_id, 'Y','Y',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
          from fii_company_hierarchies
          where parent_level= next_level
          );

Line: 1206

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');

Line: 1211

         g_phase := 'Inserting into fii_cc_pmv_agrt_nodes for CC';

Line: 1212

        Insert into fii_cc_pmv_agrt_nodes(COST_CENTER_ID,
                                for_viewby_flag,
                                aggregated_flag,
			        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
         (select next_level_cc_id, 'Y','Y',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
          from fii_cost_ctr_hierarchies
          where parent_level= next_level
         )  ;

Line: 1231

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');

Line: 1236

         g_phase := 'Inserting into fii_udd1_pmv_agrt_nodes for UDD1';

Line: 1237

        Insert into fii_udd1_pmv_agrt_nodes(UDD1_VALUE_ID,
                                for_viewby_flag,
                                aggregated_flag,
			        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
        (select next_level_value_id, 'Y','Y',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
         from fii_udd1_hierarchies
         where parent_level= next_level
        )  ;

Line: 1256

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');

Line: 1264

    SELECT MIN (no_of_children), MAX (rn)
    INTO l_subtree_freq, l_row_number
    FROM (SELECT no_of_children,
             row_number() over (order by NO_OF_CHILDREN desc) rn
          FROM FII_AGGRT_NODE_GT
          WHERE dim_short_name = p_dim_short_name)
    WHERE   rn  < =  G_OPTIMUM_NODES;

Line: 1275

    SELECT 'Y' into l_bool
    FROM (SELECT no_of_children,
             row_number() over (order by NO_OF_CHILDREN desc) rn
          FROM FII_AGGRT_NODE_GT
          WHERE dim_short_name = p_dim_short_name)
     WHERE rn > l_row_number
     AND   no_of_children = l_subtree_freq
     AND rownum <2;

Line: 1295

	g_phase := 'Inserting into fii_fc_pmv_agrt_nodes from fii_aggrt_node_gt';

Line: 1296

	     Insert into FII_FC_PMV_AGRT_NODES(FIN_CATEGORY_ID,
                                   aggregated_flag,
                                   for_viewby_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)

	      select ID, 'Y', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	      FROM FII_AGGRT_NODE_GT
	      where dim_short_name = p_dim_short_name
	      AND no_of_children >= l_subtree_freq;

Line: 1316

		   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');

Line: 1320

	g_phase := 'Inserting into fii_com_pmv_agrt_nodes from fii_aggrt_node_gt';

Line: 1321

	     Insert into FII_COM_PMV_AGRT_NODES(COMPANY_ID,
                                   aggregated_flag,
                                   for_viewby_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)

	      select ID, 'Y', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	      FROM FII_AGGRT_NODE_GT
	      where dim_short_name = p_dim_short_name
	      AND no_of_children >= l_subtree_freq;

Line: 1341

		   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');

Line: 1346

	g_phase := 'Inserting into fii_cc_pmv_agrt_nodes from fii_aggrt_node_gt';

Line: 1347

	     Insert into FII_CC_PMV_AGRT_NODES(COST_CENTER_ID,
                                   aggregated_flag,
                                   for_viewby_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)

	      select ID, 'Y', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	      FROM FII_AGGRT_NODE_GT
	      where dim_short_name = p_dim_short_name
	      AND no_of_children >= l_subtree_freq;

Line: 1367

		   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');

Line: 1372

	g_phase := 'Inserting into fii_udd1_pmv_agrt_nodes from fii_aggrt_node_gt';

Line: 1373

	     Insert into FII_UDD1_PMV_AGRT_NODES(UDD1_VALUE_ID,
                                   aggregated_flag,
                                   for_viewby_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)

	      select ID, 'Y', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	      FROM FII_AGGRT_NODE_GT
	      where dim_short_name = p_dim_short_name
	      AND no_of_children >= l_subtree_freq;

Line: 1393

		   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');

Line: 1401

       g_phase := 'Inserting records which will not be aggregated into fii_fc_pmv_agrt_nodes for FC';

Line: 1402

       Insert into fii_fc_pmv_agrt_nodes(FIN_CATEGORY_ID, for_viewby_flag,
                                aggregated_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
       (select next_level_fin_cat_id, 'N','N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
        from FII_FIN_ITEM_LEAF_HIERS
        where parent_level= next_level
        minus
        select fin_category_id, 'N', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	from fii_fc_pmv_agrt_nodes);

Line: 1427

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');

Line: 1433

        g_phase := 'Inserting records which will not be aggregated into fii_com_pmv_agrt_nodes for Company';

Line: 1434

        Insert into fii_com_pmv_agrt_nodes(company_id, for_viewby_flag,
                                aggregated_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
        (select next_level_company_id, 'N','N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	 from fii_company_hierarchies
	 where parent_level= next_level
	 minus
	 select company_id, 'N', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	from fii_com_pmv_agrt_nodes);

Line: 1459

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');

Line: 1465

        g_phase := 'Inserting records which will not be aggregated into fii_cc_pmv_agrt_nodes_gt for Cost Center';

Line: 1466

	Insert into fii_cc_pmv_agrt_nodes(cost_center_id, for_viewby_flag,
                                aggregated_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
	(select next_level_cc_id, 'N','N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	 from fii_cost_ctr_hierarchies
	 where parent_level= next_level
	 minus
	 select cost_center_id, 'N', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	from fii_cc_pmv_agrt_nodes)  ;

Line: 1491

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');

Line: 1497

         g_phase := 'Inserting records which will not be aggregated into fii_udd1_pmv_agrt_nodes for UDD1';

Line: 1498

	 Insert into fii_udd1_pmv_agrt_nodes(UDD1_VALUE_ID, for_viewby_flag,
                                aggregated_flag,
     	   		        LAST_UPDATE_DATE ,
				LAST_UPDATED_BY,
				CREATION_DATE,
				CREATED_BY,
				LAST_UPDATE_LOGIN)
	(select next_level_value_id, 'N','N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	from fii_udd1_hierarchies
	where parent_level= next_level
	minus
	select udd1_value_id, 'N', 'N',
	        SYSDATE,
	        FII_USER_ID,
		SYSDATE,
		FII_USER_ID,
		FII_LOGIN_ID
	from fii_udd1_pmv_agrt_nodes) ;

Line: 1523

	   FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');

Line: 1530

    g_phase := 'Update the Pruned hierarchy table of the dimension';

Line: 1531

    Update_pruned_table(p_dim_short_name);

Line: 1533

    g_phase := 'Update the viewby flag in the PMV Helper table of the dimension';

Line: 1536

      update_viewby_flag(p_dim_short_name);

Line: 1545

       SELECT count(*) into l_nodes_aggregated
       FROM FII_FC_PMV_AGRT_NODES
       WHERE aggregated_flag = 'Y';

Line: 1549

       SELECT count(*) into l_nodes_aggregated
       FROM FII_COM_PMV_AGRT_NODES
       WHERE aggregated_flag = 'Y';

Line: 1553

       SELECT count(*) into l_nodes_aggregated
       FROM FII_CC_PMV_AGRT_NODES
       WHERE aggregated_flag = 'Y';

Line: 1557

       SELECT count(*) into l_nodes_aggregated
       FROM FII_UDD1_PMV_AGRT_NODES
       WHERE aggregated_flag = 'Y';