[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(min(start_date), trunc(sysdate)) INTO g_min_start_date
FROM fii_time_ent_period;
SELECT nvl(max(end_date), trunc(sysdate)), count(1) INTO l_max_end_date, l_period_count
FROM fii_time_ent_period;
SELECT NVL(fii_time_api.ent_cper_START(trunc(sysdate)), l_max_end_date) INTO l_sys_month_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
SELECT NVL(fii_time_api.ent_pper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_bud_as_of_date FROM dual;
SELECT NVL(fii_time_api.ent_cper_END(g_as_of_date),l_max_end_date) INTO g_as_of_date FROM dual;
SELECT nvl(min(start_date), g_min_start_date) INTO g_curr_month_start
FROM fii_time_ent_period
WHERE g_as_of_date between start_date and END_date;
SELECT NVL( fii_time_api.sd_lyswk(g_as_of_date),g_min_start_date),
NVL(fii_time_api.ent_pyr_end(g_as_of_date),g_min_start_date),
NVL(fii_time_api.ent_cyr_end(g_as_of_date),g_min_start_date)
INTO g_py_sday,
g_ent_pyr_end,
g_ent_cyr_end
FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date), g_min_start_date) INTO g_sd_prior FROM DUAL;
SELECT NVL(fii_time_api.ent_sd_lysper_end(fii_time_api.ent_sd_lysper_end(g_as_of_date)), g_min_start_date) INTO g_sd_prior_prior FROM DUAL;
SELECT NVL(fii_time_api.ent_sd_pper_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_pper_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysper_END(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysper_END(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_cper_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cper_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
SELECT NVL(fii_time_api.ent_cper_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cper_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
SELECT ent_period_id INTO g_time_id
FROM fii_time_ent_period per
WHERE g_as_of_date BETWEEN start_date AND end_date;
SELECT NVL(fii_time_api.ent_pper_end(g_as_of_date),g_min_start_date),
NVL(fii_time_api.ent_sd_lysper_end(g_as_of_date),g_min_start_date)
INTO g_cy_period_end,
g_exp_asof_date
FROM DUAL;
SELECT DISTINCT a.sequence INTO g_curr_per_sequence
FROM fii_time_ent_period a
WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
SELECT NVL(fii_time_api.ent_sd_lysper_end(g_exp_asof_date),g_min_start_date)
INTO g_exp_start
FROM dual;
SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_pqtr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_cqtr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cqtr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
SELECT NVL(fii_time_api.ent_cqtr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cqtr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
SELECT ent_qtr_id INTO g_time_id
FROM fii_time_ent_period per
WHERE g_as_of_date BETWEEN start_date AND end_date;
SELECT NVL( fii_time_api.ent_pqtr_end(g_as_of_date),g_min_start_date) INTO g_cy_period_end FROM dual;
SELECT DISTINCT a.ent_qtr_id INTO g_curr_per_sequence
FROM fii_time_ent_qtr a
WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
INTO g_exp_asof_date
FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date)),g_min_start_date)
INTO g_exp_begin_date
FROM dual;
SELECT DISTINCT a.sequence INTO g_curr_per_sequence
FROM fii_time_ent_qtr a
WHERE g_as_of_date BETWEEN a.START_DATE AND a.END_DATE;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_as_of_date),g_min_start_date)
INTO g_exp_asof_date
FROM dual;
SELECT NVL(fii_time_api.ent_sd_lysqtr_end(g_exp_asof_date),g_min_start_date)
INTO g_exp_start
FROM dual;
SELECT NVL(fii_time_api.ent_sd_lyr_end(g_as_of_date),g_min_start_date) INTO g_previous_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_sd_lyr_end(g_bud_as_of_date),g_min_start_date) INTO g_previous_bud_asof_date FROM dual;
SELECT NVL(fii_time_api.ent_cyr_start(g_as_of_date), g_min_start_date) INTO g_curr_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cyr_end(g_as_of_date), g_min_start_date) INTO g_curr_per_end FROM DUAL;
SELECT NVL(fii_time_api.ent_cyr_start(g_previous_asof_date), g_min_start_date) INTO g_prior_per_start FROM DUAL;
SELECT NVL(fii_time_api.ent_cyr_end(g_previous_asof_date), g_min_start_date) INTO g_prior_per_end FROM DUAL;
SELECT ent_year_id INTO g_time_id
FROM fii_time_ent_period per
WHERE g_as_of_date BETWEEN start_date AND end_date;
SELECT NVL(MAX(sequence),0)
INTO g_display_sequence
FROM fii_time_ent_period
WHERE start_date >= g_curr_per_start
AND end_date <= g_as_of_date;
SELECT NVL(fii_time_api.ent_pqtr_END(g_as_of_date),g_min_start_date) INTO g_previous_one_END_date FROM dual;
SELECT NVL(fii_time_api.ent_pqtr_END(g_previous_one_END_date),g_min_start_date) INTO g_previous_two_END_date FROM dual;
SELECT NVL(fii_time_api.ent_pqtr_END(g_previous_two_END_date),g_min_start_date) INTO g_previous_three_END_date FROM dual;
SELECT NVL(fii_time_api.ent_pper_END(g_as_of_date),g_min_start_date) INTO g_previous_one_END_date FROM dual;
SELECT NVL(fii_time_api.ent_pper_END(g_previous_one_END_date),g_min_start_date) INTO g_previous_two_END_date FROM dual;
SELECT NVL(fii_time_api.ent_pper_END(g_previous_two_END_date),g_min_start_date) INTO g_previous_three_END_date FROM dual;
SELECT a.fin_category_id
FROM fii_fin_cat_type_assgns a, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
AND a.TOP_NODE_FLAG = 'Y'
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and b.is_leaf_flag = 'Y';
SELECT count(*) INTO g_fin_cat_top_node_count
FROM fii_fin_cat_type_assgns a
WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
AND a.TOP_NODE_FLAG = 'Y';
PROCEDURE insert_into_aggrt_gt IS
BEGIN
FOR a IN company_table.FIRST..company_table.LAST LOOP
FOR b IN cc_table.FIRST..cc_table.LAST LOOP
FOR c IN fin_cat_table.FIRST..fin_cat_table.LAST LOOP
FOR d IN fud1_table.FIRST..fud1_table.LAST LOOP
FOR e IN fud2_table.FIRST..fud2_table.LAST LOOP
g_aggrt_gt_record_count := g_aggrt_gt_record_count+1;
END insert_into_aggrt_gt;
PROCEDURE insert_into_non_aggrt_gt IS
BEGIN
FOR a IN company_table.FIRST..company_table.LAST LOOP
FOR b IN cc_table.FIRST..cc_table.LAST LOOP
FOR c IN fin_cat_table.FIRST..fin_cat_table.LAST LOOP
FOR d IN fud1_table.FIRST..fud1_table.LAST LOOP
FOR e IN fud2_table.FIRST..fud2_table.LAST LOOP
g_non_aggrt_gt_record_count := g_non_aggrt_gt_record_count+1;
END insert_into_non_aggrt_gt;
It also inserts records into company dimension PL/SQL table */
PROCEDURE form_all_company_join(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2,p_company_id IN NUMBER) IS
l_company_sql VARCHAR2(10000);
SELECT for_viewby_flag INTO l_comp_agg_flag
FROM fii_com_pmv_agrt_nodes
WHERE company_id = p_company_id;
SELECT parent_company_id INTO g_parent_company_id
FROM fii_company_hierarchies
WHERE child_company_id = p_company_id
and parent_level = child_level-1;
SELECT parent_company_id INTO g_parent_company_id
FROM fii_company_hierarchies
WHERE child_company_id = p_company_id
and parent_level = child_level-1;
SELECT parent_company_id INTO g_parent_company_id
FROM fii_company_hierarchies
WHERE child_company_id = p_company_id
and parent_level = child_level-1;
l_company_sql := 'SELECT co_hier.parent_company_id, co_hier.next_level_company_id, co_hier.next_level_company_sort_order, viewby_dim.description
FROM fii_company_hierarchies co_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = co_hier.next_level_company_id
and viewby_dim.language = userenv(''LANG'')
and '||l_company_join;
l_company_sql := 'SELECT co_hier.parent_company_id, co_hier.next_level_company_id, NULL,NULL
FROM fii_company_hierarchies co_hier
WHERE '||l_company_join;
company parameter. It also inserts records into company dimension PL/SQL table */
PROCEDURE form_specific_company_join(p_comp_aggregate_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2) IS
l_company_sql VARCHAR2(10000);
SELECT parent_company_id INTO g_parent_company_id
FROM fii_company_hierarchies
WHERE child_company_id = g_company_id
and parent_level = child_level-1;
SELECT parent_company_id INTO g_parent_company_id
FROM fii_company_hierarchies
WHERE child_company_id = g_company_id
and parent_level = child_level-1;
l_company_sql := 'SELECT co_hier.parent_company_id,co_hier.next_level_company_id, co_hier.next_level_company_sort_order, viewby_dim.description
FROM fii_company_hierarchies co_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = co_hier.next_level_company_id
and viewby_dim.language = userenv(''LANG'')
and '||l_company_join;
l_company_sql := 'SELECT co_hier.parent_company_id,co_hier.next_level_company_id, NULL, NULL
FROM fii_company_hierarchies co_hier
WHERE '||l_company_join;
It also inserts records into CC dimension PL/SQL table */
PROCEDURE form_all_cc_join(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2, p_cc_id IN NUMBER) IS
l_cc_sql VARCHAR2(10000);
SELECT for_viewby_flag INTO l_cc_agg_flag
FROM fii_cc_pmv_agrt_nodes
WHERE cost_center_id = p_cc_id;
SELECT parent_cc_id INTO g_parent_cost_center_id
FROM fii_cost_ctr_hierarchies
WHERE child_cc_id = p_cc_id
and parent_level = child_level-1;
SELECT parent_cc_id INTO g_parent_cost_center_id
FROM fii_cost_ctr_hierarchies
WHERE child_cc_id = p_cc_id
and parent_level = child_level-1;
SELECT parent_cc_id INTO g_parent_cost_center_id
FROM fii_cost_ctr_hierarchies
WHERE child_cc_id = p_cc_id
and parent_level = child_level-1;
l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, cc_hier.next_level_cc_sort_order, viewby_dim.description
FROM fii_cost_ctr_hierarchies cc_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = cc_hier.next_level_cc_id
and viewby_dim.language = userenv(''LANG'')
and '||l_cc_join;
l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, NULL, NULL
FROM fii_cost_ctr_hierarchies cc_hier
WHERE '||l_cc_join;
It also inserts records into CC dimension PL/SQL table */
PROCEDURE form_specific_cc_join(p_comp_agg_flag IN VARCHAR2,p_cc_agg_flag IN VARCHAR2) IS
l_cc_sql VARCHAR2(10000);
SELECT parent_cc_id INTO g_parent_cost_center_id
FROM fii_cost_ctr_hierarchies
WHERE child_cc_id = g_cost_center_id
and parent_level = child_level-1;
SELECT parent_cc_id INTO g_parent_cost_center_id
FROM fii_cost_ctr_hierarchies
WHERE child_cc_id = g_cost_center_id
and parent_level = child_level-1;
l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, cc_hier.next_level_cc_sort_order, viewby_dim.description
FROM fii_cost_ctr_hierarchies cc_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = cc_hier.next_level_cc_id
and viewby_dim.language = userenv(''LANG'')
and '||l_cc_join;
l_cc_sql := 'SELECT cc_hier.parent_cc_id, cc_hier.next_level_cc_id, NULL, NULL
FROM fii_cost_ctr_hierarchies cc_hier
WHERE '||l_cc_join;
/* below mentioned procedure inserts records into financial category, UD1 and UD2 dimension PL/SQL tables, based on their joins formed in earlier steps */
PROCEDURE other_misc_stuff(p_comp_agg_flag IN VARCHAR2, p_cc_agg_flag IN VARCHAR2, p_aggrt_gt_is_empty OUT NOCOPY VARCHAR2, p_non_aggrt_gt_is_empty OUT NOCOPY VARCHAR2) IS
l_fin_cat_sql VARCHAR2(10000);
l_fin_cat_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = fin_hier.next_level_fin_cat_id
and viewby_dim.language = userenv(''LANG'')
and '||l_cat_join;
l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = fin_hier.next_level_fin_cat_id
and viewby_dim.language = userenv(''LANG'')
and '||l_cat_join;
l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, fin_hier.next_level_fin_cat_sort_order, viewby_dim.description
FROM fii_fin_item_leaf_hiers fin_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = fin_hier.parent_fin_cat_id
and viewby_dim.language = userenv(''LANG'')
and '||l_cat_join;
l_fin_cat_sql := 'SELECT fin_hier.parent_fin_cat_id, fin_hier.next_level_fin_cat_id, NULL,NULL
FROM fii_fin_item_leaf_hiers fin_hier
WHERE '||l_cat_join;
l_fud1_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
l_fud1_sql := ' SELECT fud1_hier.parent_value_id, fud1_hier.next_level_value_id, fud1_hier.next_level_value_sort_order, viewby_dim.description
FROM fii_udd1_hierarchies fud1_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = fud1_hier.next_level_value_id
and viewby_dim.language = userenv(''LANG'')
and '||l_fud1_join;
l_fud1_sql := ' SELECT fud1_hier.parent_value_id, fud1_hier.next_level_value_id, NULL, null
FROM fii_udd1_hierarchies fud1_hier
WHERE '||l_fud1_join;
l_fud2_sql := 'SELECT NULL, NULL, NULL, NULL FROM dual';
l_fud2_sql := ' SELECT fud2_hier.parent_value_id, fud2_hier.next_level_value_id, fud2_hier.next_level_value_sort_order, viewby_dim.description
FROM fii_udd2_hierarchies fud2_hier, fnd_flex_values_tl viewby_dim
WHERE viewby_dim.flex_value_id = fud2_hier.next_level_value_id
and viewby_dim.language = userenv(''LANG'')
and '||l_fud2_join;
l_fud2_sql := ' SELECT fud2_hier.parent_value_id, fud2_hier.next_level_value_id, NULL, null
FROM fii_udd2_hierarchies fud2_hier
WHERE '||l_fud2_join;
insert_into_aggrt_gt;
insert_into_non_aggrt_gt;
SELECT sec.company_id company_id, sec.aggregated_flag agg_flag
FROM fii_company_grants sec
WHERE sec.user_id = fnd_global.user_id
and report_region_code = g_region_code;
SELECT sec.cost_center_id cc_id, sec.aggregated_flag agg_flag
FROM fii_cost_center_grants sec
WHERE sec.user_id = fnd_global.user_id
and report_region_code = g_region_code;
SELECT for_viewby_flag INTO g_ud1_aggregate_flag
FROM fii_udd1_pmv_agrt_nodes
WHERE udd1_value_id=g_fud1_id;
SELECT aggregated_flag INTO g_ud1_aggregate_flag FROM fii_udd1_pmv_agrt_nodes WHERE udd1_value_id=g_fud1_id;
SELECT for_viewby_flag INTO g_fin_aggregate_flag FROM fii_fc_pmv_agrt_nodes WHERE fin_category_id=g_fin_category_id;
SELECT aggregated_flag INTO g_fin_aggregate_flag FROM fii_fc_pmv_agrt_nodes WHERE fin_category_id=g_fin_category_id;
/* bug 4337351. For non-viewby fin category scenarios, while inserting records into fii_pmv_aggrt_gt table,
we earlier picked up top node as parent fin category id and its next-level children as child fin category ids.
This resulted in more NUMBER of records being inserted in gt tables which degraded performance. Instead, now,
for non-viewby category cases, we pick up the combination of Operating Margin-Top node(s) which results in significant perf improvement */
IF g_view_by <> 'FINANCIAL ITEM+GL_FII_FIN_ITEM' THEN
l_aggrt_cat_join := 'EXISTS ( SELECT 1
FROM fii_fin_cat_type_assgns a
, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
AND a.TOP_NODE_FLAG = ''Y''
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and a.fin_category_id <> b.PARENT_FIN_CAT_ID
AND a.fin_Category_id = fin_hier.child_fin_cat_id
) and (child_level <> parent_level AND child_level = parent_level+1)';
l_aggrt_cat_join := 'EXISTS ( SELECT 1
FROM fii_fin_cat_type_assgns a
, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
AND a.TOP_NODE_FLAG = ''Y''
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and a.fin_category_id <> b.PARENT_FIN_CAT_ID
AND a.fin_Category_id = fin_hier.parent_fin_cat_id
) and (child_level = parent_level OR child_level = parent_level+1)';
l_aggrt_cat_join := 'EXISTS ( SELECT 1
FROM fii_fin_cat_type_assgns a
, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
AND a.TOP_NODE_FLAG = ''Y''
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and a.fin_category_id <> b.PARENT_FIN_CAT_ID
AND a.fin_Category_id = fin_hier.child_fin_cat_id
) and (child_level <> parent_level AND child_level = parent_level+1)';
SELECT a.fin_category_id INTO g_category_id
FROM fii_fin_cat_type_assgns a
WHERE a.FIN_CAT_TYPE_CODE = g_fin_cat_type
and a.TOP_NODE_FLAG = 'Y';
l_nonaggrt_cat_join := 'EXISTS ( SELECT 1
FROM fii_fin_cat_type_assgns a
, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
AND a.TOP_NODE_FLAG = ''Y''
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and a.fin_category_id <> b.PARENT_FIN_CAT_ID
AND a.fin_Category_id = fin_hier.parent_fin_cat_id
) and (child_level = parent_level OR child_level = parent_level+1)';
l_nonaggrt_cat_join := 'EXISTS ( SELECT 1
FROM fii_fin_cat_type_assgns a
, fii_fin_item_leaf_hiers b
WHERE a.FIN_CAT_TYPE_CODE = :g_fin_cat_type
AND a.TOP_NODE_FLAG = ''Y''
and a.fin_category_id = b.CHILD_FIN_CAT_ID
and a.fin_category_id <> b.PARENT_FIN_CAT_ID
AND a.fin_Category_id = fin_hier.parent_fin_cat_id
) and child_level = parent_level';
SELECT parent_fin_cat_id INTO g_parent_fin_category_id
FROM fii_fin_item_leaf_hiers
WHERE child_fin_cat_id = g_fin_category_id
and parent_level=child_level-1;
SELECT parent_fin_cat_id INTO g_parent_fin_category_id
FROM fii_fin_item_leaf_hiers
WHERE child_fin_cat_id = g_fin_category_id
and parent_level=child_level-1;
SELECT is_leaf_flag INTO l_leaf_flag
FROM fii_fin_item_leaf_hiers
WHERE parent_fin_cat_id=g_fin_category_id
and parent_fin_cat_id = child_fin_cat_id;
SELECT dbi_enabled_flag INTO l_fud1_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
SELECT parent_value_id INTO g_top_fud1_id
FROM fii_udd1_hierarchies;
SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud1_id
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
SELECT parent_value_id INTO g_parent_fud1_id
FROM fii_udd1_hierarchies
WHERE child_value_id = g_fud1_id
and parent_level = child_level-1;
SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud1_id
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
SELECT parent_value_id INTO g_parent_fud1_id
FROM fii_udd1_hierarchies
WHERE child_value_id = g_fud1_id
and parent_level=child_level-1;
SELECT dbi_enabled_flag INTO l_fud2_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
SELECT parent_value_id INTO g_top_fud2_id
FROM fii_udd2_hierarchies;
SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud2_id
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
SELECT NVL(dbi_hier_top_node_id, -99999) INTO g_top_fud2_id
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
SELECT count(*) INTO g_company_count
FROM fii_company_grants
WHERE user_id = fnd_global.user_id
and report_region_code = g_region_code;
SELECT count(*) INTO g_cc_count
FROM fii_cost_center_grants
WHERE user_id = fnd_global.user_id
and report_region_code = g_region_code;
INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(z),cc_id_nonaggrt_plsql(z),fin_cat_id_nonaggrt_plsql(z),
fud1_id_nonaggrt_plsql(z),fud2_id_nonaggrt_plsql(z),nonaggrt_viewbydescription(z),sort_order_nonaggrt_plsql(z));
insert_into_debug_tables;
SELECT for_viewby_flag
INTO l_cc_aggregate_flag
FROM fii_cc_pmv_agrt_nodes
WHERE cost_center_id=g_cost_center_id;
SELECT aggregated_flag INTO l_cc_aggregate_flag FROM fii_cc_pmv_agrt_nodes id
WHERE cost_center_id=g_cost_center_id;
INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(z),cc_id_nonaggrt_plsql(z),fin_cat_id_nonaggrt_plsql(z),
fud1_id_nonaggrt_plsql(z),fud2_id_nonaggrt_plsql(z),nonaggrt_viewbydescription(z),sort_order_nonaggrt_plsql(z));
insert_into_debug_tables;
SELECT count(*) INTO g_cc_count
FROM fii_cost_center_grants
WHERE user_id=fnd_global.user_id
and report_region_code = g_region_code;
SELECT for_viewby_flag
INTO l_company_aggregate_flag
FROM fii_com_pmv_agrt_nodes
WHERE company_id=g_company_id;
SELECT aggregated_flag INTO l_company_aggregate_flag
FROM fii_com_pmv_agrt_nodes
WHERE company_id=g_company_id;
INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(i),cc_id_nonaggrt_plsql(i),fin_cat_id_nonaggrt_plsql(i),
fud1_id_nonaggrt_plsql(i),fud2_id_nonaggrt_plsql(i),nonaggrt_viewbydescription(i),sort_order_nonaggrt_plsql(i));
insert_into_debug_tables;
SELECT for_viewby_flag
INTO l_company_aggregate_flag
FROM fii_com_pmv_agrt_nodes
WHERE company_id = g_company_id;
SELECT aggregated_flag INTO l_company_aggregate_flag
FROM fii_com_pmv_agrt_nodes
WHERE company_id=g_company_id;
SELECT for_viewby_flag
INTO l_cc_aggregate_flag
FROM fii_cc_pmv_agrt_nodes
WHERE cost_center_id = g_cost_center_id;
SELECT aggregated_flag INTO l_cc_aggregate_flag
FROM fii_cc_pmv_agrt_nodes
WHERE cost_center_id=g_cost_center_id;
INSERT INTO fii_pmv_aggrt_gt VALUES ( par_comp_id_aggrt_plsql(y),comp_id_aggrt_plsql(y),par_cc_id_aggrt_plsql(y),
cc_id_aggrt_plsql(y),par_fin_cat_id_aggrt_plsql(y),fin_cat_id_aggrt_plsql(y),
par_fud1_id_aggrt_plsql(y),fud1_id_aggrt_plsql(y),fud2_id_aggrt_plsql(y),
aggrt_viewbydescription(y), sort_order_aggrt_plsql(y));
INSERT INTO fii_pmv_non_aggrt_gt VALUES (comp_id_nonaggrt_plsql(i),cc_id_nonaggrt_plsql(i),fin_cat_id_nonaggrt_plsql(i),
fud1_id_nonaggrt_plsql(i),fud2_id_nonaggrt_plsql(i),nonaggrt_viewbydescription(i),sort_order_nonaggrt_plsql(i));
insert_into_debug_tables;
PROCEDURE insert_into_debug_tables IS
/* logic for this api...
1. We first search for existence of debug table. If it doesn't exist, we create it else we delete the records inserted for the same session_id and region code.
2. We then insert all columns of corresponding gt table + session_id and report_region_code into debug tables.
*/
l_agrt_table_count NUMBER := 0;
IF g_aggrt_gt_record_count > 0 THEN -- it means that fii_pmv_aggrt_gt has been populated so only now, we should insert records into fii_debug_agrt table
BEGIN
SELECT 1 INTO l_agrt_table_count
FROM dba_tables
WHERE table_name = 'FII_DEBUG_AGRT'
and owner = l_schema_name;
EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_DEBUG_AGRT WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_DEBUG_AGRT (PARENT_COMPANY_ID,
COMPANY_ID,
PARENT_CC_ID,
CC_ID,
PARENT_FIN_CATEGORY_ID,
FIN_CATEGORY_ID,
PARENT_FUD1_ID,
FUD1_ID,
FUD2_ID,
VIEWBY,
SORT_ORDER,
SESSION_ID,
REGION_CODE)
SELECT gt.PARENT_COMPANY_ID,
gt.COMPANY_ID,
gt.PARENT_CC_ID,
gt.CC_ID ,
gt.PARENT_FIN_CATEGORY_ID,
gt.FIN_CATEGORY_ID,
gt.PARENT_FUD1_ID,
gt.FUD1_ID,
gt.FUD2_ID,
gt.VIEWBY,
gt.SORT_ORDER,
'||g_session_id||','''||g_region_code||'''
FROM fii_pmv_aggrt_gt gt';
IF g_non_aggrt_gt_record_count > 0 THEN -- it means that fii_pmv_non_aggrt_gt has been populated so only now, we should insert records into fii_debug_non_agrt table
BEGIN
SELECT 1 INTO l_non_agrt_table_count
FROM dba_tables
WHERE table_name = 'FII_DEBUG_NON_AGRT'
and owner = l_schema_name;
EXECUTE IMMEDIATE 'DELETE FROM '||l_schema_name||'.FII_DEBUG_NON_AGRT WHERE REGION_CODE = '''||g_region_code||''' AND SESSION_ID = '||g_session_id;
EXECUTE IMMEDIATE 'INSERT INTO '||l_schema_name||'.FII_DEBUG_NON_AGRT (COMPANY_ID,
COST_CENTER_ID,
FIN_CATEGORY_ID,
FUD1_ID,
FUD2_ID,
VIEWBY,
SORT_ORDER,
SESSION_ID,
REGION_CODE)
SELECT gt.company_id,
gt.cost_center_id,
gt.fin_category_id,
gt.fud1_id,
gt.fud2_id,
gt.viewby,
gt.sort_order,
'||g_session_id||','''||g_region_code||'''
FROM fii_pmv_non_aggrt_gt gt';
END insert_into_debug_tables;
SELECT name INTO stmt
FROM fii_time_ent_year
WHERE p_as_of_date between start_date and end_date;
SELECT name INTO stmt
FROM fii_time_ent_qtr
WHERE p_as_of_date between start_date and end_date;
SELECT name INTO stmt
FROM fii_time_ent_period
WHERE p_as_of_date between start_date and end_date;
SELECT dbi_enabled_flag INTO l_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
l_stmt := ' and f.fud1_id in (SELECT child_value_id FROM fii_full_udd1_hiers WHERE parent_value_id = -999)';
l_stmt := ' and f.fud1_id in (SELECT child_value_id FROM fii_full_udd1_hiers WHERE parent_value_id = &FII_USER_DEFINED+FII_USER_DEFINED_1)';
SELECT dbi_enabled_flag INTO l_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
l_stmt := ' and f.fud2_id in (SELECT child_value_id FROM fii_full_udd2_hiers WHERE parent_value_id = &FII_USER_DEFINED+FII_USER_DEFINED_2)';
SELECT name INTO stmt
FROM fii_time_ent_qtr
WHERE l_temp_date = END_date;
SELECT name INTO stmt
FROM fii_time_ent_period
WHERE l_temp_date = END_date;
SELECT is_leaf_flag INTO g_company_is_leaf
FROM fii_company_hierarchies
WHERE parent_company_id = p_id
and parent_company_id = child_company_id;
SELECT is_leaf_flag INTO g_cost_center_is_leaf
FROM fii_cost_ctr_hierarchies
WHERE parent_cc_id = p_id
and parent_cc_id = child_cc_id;
SELECT is_leaf_flag INTO g_fin_cat_is_leaf
FROM fii_fin_item_leaf_hiers
WHERE parent_fin_cat_id = p_id
and parent_fin_cat_id = child_fin_cat_id;
SELECT is_leaf_flag INTO g_ud1_is_leaf
FROM fii_udd1_hierarchies
WHERE parent_value_id = p_id
and parent_value_id = child_value_id;
SELECT is_leaf_flag INTO g_ud2_is_leaf
FROM fii_udd1_hierarchies
WHERE parent_value_id = p_id
and parent_value_id = child_value_id;