[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(fii_ea_util_pkg.g_region_code,'FII_EA_EXP_SUM','FII_EA_EXP_SUM','FII_PSI_EXP_SUM')
INTO l_drill_source
FROM DUAL;
SELECT dbi_enabled_flag INTO l_fud2_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
'||p_aggrt_viewby_id||' viewby_id,
inner_inline_view.viewby viewby,
inner_inline_view.sort_order sort_order,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL1,
SUM(DECODE(inner_inline_view.report_date,:PREVIOUS_TWO_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL2,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL3,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL4,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_XTD_EXP,
'||l_bud_frcst_prior||'
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
THEN f.budget_g ELSE NULL END) ) ) FII_EA_BUDGET,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
THEN f.forecast_g ELSE NULL END) ) ) FII_EA_FORECAST
FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
'||l_fud2_from||'
( select /*+ NO_MERGE cardinality(gt 1) */ *
from fii_time_structures cal,
fii_pmv_aggrt_gt gt
where report_date in ( :PREVIOUS_ONE_END_DATE, :PREVIOUS_TWO_END_DATE,
:PREVIOUS_THREE_END_DATE, :ASOF_DATE,
:PREVIOUS_ASOF_DATE
)
and ( bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
bitand(cal.record_type_id, :HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND OR
bitand(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
bitand(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
)
) inner_inline_view
WHERE f.time_id = inner_inline_view.time_id
and f.period_type_id = inner_inline_view.period_type_id
and f.parent_company_id = inner_inline_view.parent_company_id
and f.company_id = inner_inline_view.company_id
and f.parent_cost_center_id = inner_inline_view.parent_cc_id
and f.cost_center_id = inner_inline_view.cc_id
and f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
and f.fin_category_id = inner_inline_view.fin_category_id
'||l_budget_decode||'
and f.parent_fud1_id = inner_inline_view.parent_fud1_id
and f.fud1_id = inner_inline_view.fud1_id
'||l_fud2_where||'
GROUP BY '||p_aggrt_viewby_id||',
inner_inline_view.viewby,
inner_inline_view.sort_order';
SELECT /*+ index(f fii_gl_base_map_mv_n1) */
'||p_nonaggrt_viewby_id||' viewby_id,
inner_inline_view.viewby viewby,
inner_inline_view.sort_order sort_order,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL1,
SUM(DECODE(inner_inline_view.report_date,:PREVIOUS_TWO_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL2,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL3,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL4,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_XTD_EXP,
'||l_bud_frcst_prior||'
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
THEN f.budget_g ELSE NULL END) ) ) FII_EA_BUDGET,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
THEN f.forecast_g ELSE NULL END) ) ) FII_EA_FORECAST
FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_company_hierarchies co_hier,
fii_cost_ctr_hierarchies cc_hier,
fii_fin_item_leaf_hiers fin_hier,
fii_udd1_hierarchies fud1_hier,
'||l_fud2_from||'
( select /*+ NO_MERGE cardinality(gt 1) */ *
from fii_time_structures cal,
fii_pmv_non_aggrt_gt gt
where report_date in ( :PREVIOUS_ONE_END_DATE, :PREVIOUS_TWO_END_DATE,
:PREVIOUS_THREE_END_DATE, :ASOF_DATE,
:PREVIOUS_ASOF_DATE
)
and ( bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
bitand(cal.record_type_id, :HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND OR
bitand(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
bitand(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
)
) inner_inline_view
WHERE f.period_type_id = inner_inline_view.period_type_id
and f.time_id = inner_inline_view.time_id
and co_hier.parent_company_id = inner_inline_view.company_id
and co_hier.child_company_id = f.company_id
and cc_hier.parent_cc_id = inner_inline_view.cost_center_id
and cc_hier.child_cc_id = f.cost_center_id
and fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
'||l_cat_decode||'
'||l_budget_decode||'
and fin_hier.child_fin_cat_id = f.fin_category_id
and fud1_hier.parent_value_id = inner_inline_view.fud1_id
'||l_fud1_decode||'
and fud1_hier.child_value_id = f.fud1_id
'||l_fud2_where||'
GROUP BY '||p_nonaggrt_viewby_id||',
inner_inline_view.viewby,
inner_inline_view.sort_order';
SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
'||p_snap_aggrt_viewby_id||' viewby_id,
gt.viewby viewby,
gt.sort_order sort_order,
NULL FII_EA_HIST_COL1,
NULL FII_EA_HIST_COL2,
NULL FII_EA_HIST_COL3,
SUM(f.actual_cur_'||l_roll_column||') FII_EA_HIST_COL4,
SUM(f.actual_cur_'||l_xtd_column||') FII_EA_XTD_EXP,
'||l_snap_prior||'
SUM(f.budget_cur_'||l_xtd_column||') FII_EA_BUDGET,
SUM(f.forecast_cur_'||l_xtd_column||') FII_EA_FORECAST
FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f,
'||l_fud2_from||'
fii_pmv_aggrt_gt gt
WHERE f.parent_company_id = gt.parent_company_id
and f.company_id = gt.company_id
and f.parent_cost_center_id = gt.parent_cc_id
and f.cost_center_id =gt.cc_id
and f.parent_fin_category_id = gt.parent_fin_category_id
and f.fin_category_id = gt.fin_category_id
'||l_budget_snap_decode||'
and f.parent_fud1_id = gt.parent_fud1_id
and f.fud1_id =gt.fud1_id
'||l_fud2_snap_where||'
GROUP BY '||p_snap_aggrt_viewby_id||', gt.viewby, gt.sort_order
UNION ALL
/* This query returns HIST1, HIST2 and HIST3 values for aggregated nodes */
SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
'||p_aggrt_viewby_id||' viewby_id,
inner_inline_view.viewby viewby,
inner_inline_view.sort_order sort_order,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END))) FII_EA_HIST_COL1,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END))) FII_EA_HIST_COL2,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END))) FII_EA_HIST_COL3,
NULL FII_EA_HIST_COL4,
NULL FII_EA_XTD_EXP,
NULL FII_EA_PRIOR_XTD_EXP_G,
NULL FII_EA_PRIOR_XTD_EXP,
NULL FII_EA_PRIOR_BUDGET,
NULL FII_EA_PRIOR_FORECAST,
SUM(DECODE(inner_inline_view.report_date, :PRIOR_PERIOD_END,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END))) FII_EA_PRIOR_TOTAL_G,
SUM(DECODE(inner_inline_view.report_date, :CURR_PERIOD_END,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END))) FII_EA_CURR_TOTAL_G,
NULL FII_EA_BUDGET,
NULL FII_EA_FORECAST
FROM fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
'||l_fud2_from||'
( select /*+ NO_MERGE cardinality(gt 1) */ *
from fii_time_structures cal,
fii_pmv_aggrt_gt gt
where report_date in ( :PREVIOUS_ONE_END_DATE, :PREVIOUS_TWO_END_DATE,
:PREVIOUS_THREE_END_DATE
)
and ( BITAND(cal.record_type_id, :HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND OR
BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
)
) inner_inline_view
WHERE f.time_id = inner_inline_view.time_id
and f.period_type_id = inner_inline_view.period_type_id
and f.parent_company_id = inner_inline_view.parent_company_id
and f.company_id = inner_inline_view.company_id
and f.parent_cost_center_id = inner_inline_view.parent_cc_id
and f.cost_center_id = inner_inline_view.cc_id
and f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
and f.fin_category_id = inner_inline_view.fin_category_id
'||l_budget_decode||'
and f.parent_fud1_id = inner_inline_view.parent_fud1_id
and f.fud1_id = inner_inline_view.fud1_id
'||l_fud2_where||'
GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
SELECT /*+ index(f fii_gl_base_map_mv_n1) */
'||p_nonaggrt_viewby_id||' viewby_id,
inner_inline_view.viewby viewby,
inner_inline_view.sort_order sort_order,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL1,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL2,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL3,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL4,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_XTD_EXP,
'||l_bud_frcst_prior||'
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
THEN f.budget_g ELSE NULL END) ) ) FII_EA_BUDGET,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
THEN f.forecast_g ELSE NULL END) ) ) FII_EA_FORECAST
FROM fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_company_hierarchies co_hier,
fii_cost_ctr_hierarchies cc_hier,
fii_fin_item_leaf_hiers fin_hier,
fii_udd1_hierarchies fud1_hier,
'||l_fud2_from||'
( select /*+ NO_MERGE cardinality(gt 1) */ *
from fii_time_structures cal,
fii_pmv_non_aggrt_gt gt
where report_date in ( :PREVIOUS_ONE_END_DATE, :PREVIOUS_TWO_END_DATE,
:PREVIOUS_THREE_END_DATE, :ASOF_DATE,
:PREVIOUS_ASOF_DATE
)
and ( bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
bitand(cal.record_type_id, :HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND OR
bitand(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
bitand(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
)
) inner_inline_view
WHERE f.period_type_id = inner_inline_view.period_type_id
and f.time_id = inner_inline_view.time_id
and co_hier.parent_company_id = inner_inline_view.company_id
and co_hier.child_company_id = f.company_id
and cc_hier.parent_cc_id = inner_inline_view.cost_center_id
and cc_hier.child_cc_id = f.cost_center_id
and fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
'||l_cat_decode||'
and fin_hier.child_fin_cat_id = f.fin_category_id
and fud1_hier.parent_value_id = inner_inline_view.fud1_id
'||l_fud1_decode||'
'||l_budget_decode||'
and fud1_hier.child_value_id = f.fud1_id
'||l_fud2_where||'
GROUP BY '||p_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
SELECT '||p_aggrt_viewby_id||' viewby_id,
inner_inline_view.viewby viewby,
inner_inline_view.sort_order sort_order,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_THREE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL1,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL2,
SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL3,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_HIST_COL4,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
THEN f.actual_g ELSE NULL END) ) ) FII_EA_XTD_EXP,
'||l_bud_frcst_prior||'
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:BUDGET_BITAND) = :BUDGET_BITAND
THEN f.budget_g ELSE NULL END) ) ) FII_EA_BUDGET,
SUM(DECODE(inner_inline_view.report_date, :ASOF_DATE,
(CASE WHEN bitand(inner_inline_view.record_type_id,:FORECAST_BITAND) = :FORECAST_BITAND
THEN f.forecast_g ELSE NULL END) ) ) FII_EA_FORECAST
FROM fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
( SELECT /*+ NO_MERGE cardinality(gt 1) */ *
FROM fii_time_structures cal,
fii_pmv_aggrt_gt gt
where report_date in ( :PREVIOUS_ONE_END_DATE, :PREVIOUS_TWO_END_DATE,
:PREVIOUS_THREE_END_DATE, :ASOF_DATE,
:PREVIOUS_ASOF_DATE
)
and ( bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND OR
bitand(cal.record_type_id, :HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND OR
bitand(cal.record_type_id, :BUDGET_BITAND) = :BUDGET_BITAND OR
bitand(cal.record_type_id, :FORECAST_BITAND) = :FORECAST_BITAND
)
) inner_inline_view
WHERE f.time_id = inner_inline_view.time_id
AND f.period_type_id = inner_inline_view.period_type_id
AND f.parent_company_id = inner_inline_view.parent_company_id
AND f.company_id = inner_inline_view.company_id
AND f.parent_cost_center_id = inner_inline_view.parent_cc_id
AND f.cost_center_id = inner_inline_view.cc_id
AND f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
AND f.fin_category_id = inner_inline_view.fin_category_id
'||l_budget_decode||'
GROUP BY '||p_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order';
SELECT DECODE(:G_ID, inline_view.viewby_id,DECODE('''||l_if_leaf_flag||''',''Y'',
inline_view.viewby, inline_view.viewby||'' ''||:DIR_MSG),
inline_view.viewby) VIEWBY,
inline_view.viewby_id VIEWBYID,
SUM(FII_EA_PRIOR_XTD_EXP_G) FII_EA_PRIOR_XTD_EXP_G,
SUM(FII_EA_PRIOR_TOTAL_G) FII_EA_PRIOR_TOTAL_G,
SUM(FII_EA_XTD_EXP) FII_EA_XTD_EXP,
SUM(FII_EA_CURR_TOTAL_G) FII_EA_CURR_TOTAL_G,
SUM(FII_EA_PRIOR_XTD_EXP) FII_EA_PRIOR_XTD_EXP,
SUM(FII_EA_PRIOR_BUDGET) FII_EA_PRIOR_BUDGET,
SUM(FII_EA_PRIOR_FORECAST) FII_EA_PRIOR_FORECAST,
SUM(FII_EA_BUDGET) FII_EA_BUDGET,
SUM(FII_EA_FORECAST) FII_EA_FORECAST,
SUM(FII_EA_HIST_COL1) FII_EA_HIST_COL1,
SUM(FII_EA_HIST_COL2) FII_EA_HIST_COL2,
SUM(FII_EA_HIST_COL3) FII_EA_HIST_COL3,
SUM(FII_EA_HIST_COL4) FII_EA_HIST_COL4,
SUM(SUM(FII_EA_XTD_EXP)) OVER () FII_EA_GT_XTD_EXP,
SUM(SUM(FII_EA_PRIOR_XTD_EXP)) OVER () FII_EA_GT_PRIOR_XTD_EXP,
(SUM(SUM(FII_EA_XTD_EXP)) over() -
SUM(SUM(FII_EA_PRIOR_XTD_EXP)) over()) /
ABS(NULLIF(SUM(SUM(FII_EA_PRIOR_XTD_EXP)) over(),0)) * 100 FII_EA_GT_CHANGE,
SUM(SUM(FII_EA_BUDGET)) OVER () FII_EA_GT_BUDGET,
NULLIF(SUM(SUM(FII_EA_PRIOR_BUDGET)) OVER (),0) FII_EA_GT_PRIOR_BUDGET,
SUM(SUM(FII_EA_XTD_EXP)) OVER () /
NULLIF(SUM(SUM(FII_EA_BUDGET)) OVER (),0) * 100 FII_EA_GT_PCNT_BUDGET,
SUM(SUM(FII_EA_FORECAST)) OVER () FII_EA_GT_FORECAST,
NULLIF(SUM(SUM(FII_EA_PRIOR_FORECAST)) OVER (),0) FII_EA_GT_PRIOR_FORECAST,
SUM(SUM(FII_EA_XTD_EXP)) OVER () /
NULLIF(SUM(SUM(FII_EA_FORECAST)) OVER (),0) * 100 FII_EA_GT_PCNT_FORECAST,
SUM(SUM(FII_EA_HIST_COL1)) OVER () FII_EA_GT_HIST_COL1,
SUM(SUM(FII_EA_HIST_COL2)) OVER () FII_EA_GT_HIST_COL2,
SUM(SUM(FII_EA_HIST_COL3)) OVER () FII_EA_GT_HIST_COL3,
SUM(SUM(FII_EA_HIST_COL4)) OVER () FII_EA_GT_HIST_COL4,
DECODE((SELECT is_leaf_flag
FROM fii_company_hierarchies
WHERE parent_company_id = inline_view.viewby_id
and child_company_id = inline_view.viewby_id),
''Y'',
'''',
'''||l_viewby_drill_url||''') FII_EA_COMP_DRILL,
DECODE((SELECT is_leaf_flag
FROM fii_cost_ctr_hierarchies
WHERE parent_cc_id = inline_view.viewby_id
and child_cc_id = inline_view.viewby_id),
''Y'',
'''',
'''||l_viewby_drill_url||''') FII_EA_CC_DRILL,
DECODE((SELECT is_leaf_flag
FROM fii_fin_item_leaf_hiers
WHERE parent_fin_cat_id = inline_view.viewby_id
and child_fin_cat_id = inline_view.viewby_id),
''Y'',
'''',
DECODE(:G_ID, inline_view.viewby_id,'''',
'''||l_viewby_drill_url||''')) FII_EA_CAT_DRILL,
DECODE((SELECT is_leaf_flag
FROM fii_udd1_hierarchies
WHERE parent_value_id = inline_view.viewby_id
and child_value_id = inline_view.viewby_id),
''Y'',
'''',
DECODE(:G_ID, inline_view.viewby_id,'''',
'''||l_viewby_drill_url||''')) FII_EA_UDD1_DRILL,
DECODE((SELECT is_leaf_flag
FROM fii_udd2_hierarchies
WHERE parent_value_id = inline_view.viewby_id
and child_value_id = inline_view.viewby_id),
''Y'',
'''',
'''||l_viewby_drill_url||''') FII_EA_UDD2_DRILL,
DECODE(SUM(FII_EA_XTD_EXP),0,'''',DECODE(NVL(SUM(FII_EA_XTD_EXP),-999999),-999999,'''','''||l_xtd_drill_url||''')) FII_EA_XTD_DRILL,
DECODE(SUM(FII_EA_XTD_EXP),0,'''',DECODE(NVL(SUM(FII_EA_XTD_EXP),-999999),-999999,'''','''||l_xtd_drill_url||'''))
FII_EA_XTD_PIE_DRILL
FROM
(
'||l_aggrt_sql||'
'||l_union_all||'
'||l_non_aggrt_sql||'
) inline_view
GROUP BY inline_view.viewby, inline_view.viewby_id, inline_view.sort_order
ORDER BY NVL(inline_view.sort_order,999999) asc, NVL(FII_EA_XTD_EXP, -999999999) desc'; /* Done for bug 4093082 */
SELECT DISTINCT per.sequence INTO l_curr_per_sequence
FROM FII_TIME_ENT_PERIOD per
WHERE fii_ea_util_pkg.g_as_of_date BETWEEN per.start_date and per.end_date;
SELECT end_date INTO l_curr_end_date
FROM fii_time_ent_period
WHERE fii_ea_util_pkg.g_as_of_date between start_date and end_date;
SELECT dbi_enabled_flag
INTO l_fud2_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';
SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
'||l_curr_per_sequence||' FII_EFFECTIVE_NUM,
f.actual_cur_mtd FII_EA_XTD,
f.actual_last_year_mtd FII_EA_PRIOR_XTD
FROM fii_gl_snap_sum_f'||fii_ea_util_pkg.g_curr_view||' f ,
'||l_fud2_from||'
fii_pmv_aggrt_gt gt
WHERE f.parent_company_id = gt.parent_company_id
and f.company_id = gt.company_id
and f.parent_cost_center_id = gt.parent_cc_id
and f.cost_center_id = gt.cc_id
and f.parent_fin_category_id = gt.parent_fin_category_id
and f.fin_category_id = gt.fin_category_id
and f.parent_fud1_id = gt.parent_fud1_id
and f.fud1_id = gt.fud1_id
'||l_fud2_where||l_ledger_where;
SELECT /*+ index(f fii_gl_base_map_mv_n1) */
'||l_curr_per_sequence||' FII_EFFECTIVE_NUM,
CASE WHEN cal.report_date = :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN cal.report_date = :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_structures cal,
fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_company_hierarchies co_hier,
fii_cost_ctr_hierarchies cc_hier,
fii_fin_item_leaf_hiers fin_hier,
fii_udd1_hierarchies fud1_hier,
'||l_fud2_from||'
fii_pmv_non_aggrt_gt gt
WHERE cal.time_id = f.time_id
and cal.report_date in (:ASOF_DATE,:SD_PRIOR)
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND
and co_hier.parent_company_id = gt.company_id
and co_hier.child_company_id = f.company_id
and cc_hier.parent_cc_id = gt.cost_center_id
and cc_hier.child_cc_id = f.cost_center_id
and fin_hier.parent_fin_cat_id = gt.fin_category_id
and fin_hier.child_fin_cat_id = f.fin_category_id
and fud1_hier.parent_value_id = gt.fud1_id
and fud1_hier.child_value_id = f.fud1_id
'||l_fud2_where||l_ledger_where;
SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
per.sequence FII_EFFECTIVE_NUM,
CASE WHEN per.start_date > :SD_PRIOR
and per.end_date <= :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN per.end_date <= :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_ent_period per,
fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
'||l_fud2_from||'
fii_pmv_aggrt_gt gt
WHERE per.ent_period_id = f.time_id
and f.period_type_id = 32
and per.start_date > :SD_PRIOR_PRIOR
and per.end_date <= :ASOF_DATE
and f.parent_company_id = gt.parent_company_id
and f.company_id = gt.company_id
and f.parent_cost_center_id = gt.parent_cc_id
and f.cost_center_id = gt.cc_id
and f.parent_fin_category_id = gt.parent_fin_category_id
and f.fin_category_id = gt.fin_category_id
and f.parent_fud1_id = gt.parent_fud1_id
and f.fud1_id = gt.fud1_id
'||l_fud2_where||l_ledger_where||l_union_agrt_snap_sql;
SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
per.sequence FII_EFFECTIVE_NUM,
CASE WHEN per.start_date > :SD_PRIOR
and per.end_date <= :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN per.end_date <= :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_ent_period per,
fii_gl_agrt_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
'||l_fud2_from||'
fii_pmv_aggrt_gt gt
WHERE per.ent_period_id = f.time_id
and f.period_type_id = 32
and per.start_date > :SD_PRIOR_PRIOR
and per.end_date <= :ASOF_DATE
and f.parent_company_id = gt.parent_company_id
and f.company_id = gt.company_id
and f.parent_cost_center_id = gt.parent_cc_id
and f.cost_center_id = gt.cc_id
and f.parent_fin_category_id = gt.parent_fin_category_id
and f.fin_category_id = gt.fin_category_id
and f.parent_fud1_id = gt.parent_fud1_id
and f.fud1_id = gt.fud1_id
'||l_fud2_where||l_ledger_where;
SELECT /*+ index(f fii_gl_base_map_mv_n1) */
per.sequence FII_EFFECTIVE_NUM,
CASE WHEN per.start_date > :SD_PRIOR
and per.end_date <= :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN per.end_date <= :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_ent_period per,
fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_company_hierarchies co_hier,
fii_cost_ctr_hierarchies cc_hier,
fii_fin_item_leaf_hiers fin_hier,
fii_udd1_hierarchies fud1_hier,
'||l_fud2_from||'
fii_pmv_non_aggrt_gt gt
WHERE per.ent_period_id = f.time_id
and f.period_type_id = 32
and per.start_date > :SD_PRIOR_PRIOR
and per.end_date <= :ASOF_DATE
and co_hier.parent_company_id = gt.company_id
and co_hier.child_company_id = f.company_id
and cc_hier.parent_cc_id = gt.cost_center_id
and cc_hier.child_cc_id = f.cost_center_id
and fin_hier.parent_fin_cat_id = gt.fin_category_id
and fin_hier.child_fin_cat_id = f.fin_category_id
and fud1_hier.parent_value_id = gt.fud1_id
and fud1_hier.child_value_id = f.fud1_id
'||l_fud2_where||l_ledger_where||l_union_base_snap_sql;
SELECT /*+ index(f fii_gl_base_map_mv_n1) */
per.sequence FII_EFFECTIVE_NUM,
CASE WHEN per.start_date > :SD_PRIOR
and per.end_date <= :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN per.end_date <= :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_ent_period per,
fii_gl_base_map_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_company_hierarchies co_hier,
fii_cost_ctr_hierarchies cc_hier,
fii_fin_item_leaf_hiers fin_hier,
fii_udd1_hierarchies fud1_hier,
'||l_fud2_from||'
fii_pmv_non_aggrt_gt gt
WHERE per.ent_period_id = f.time_id
and f.period_type_id = 32
and per.start_date > :SD_PRIOR_PRIOR
and per.end_date <= :ASOF_DATE
and co_hier.parent_company_id = gt.company_id
and co_hier.child_company_id = f.company_id
and cc_hier.parent_cc_id = gt.cost_center_id
and cc_hier.child_cc_id = f.cost_center_id
and fin_hier.parent_fin_cat_id = gt.fin_category_id
and fin_hier.child_fin_cat_id = f.fin_category_id
and fud1_hier.parent_value_id = gt.fud1_id
and fud1_hier.child_value_id = f.fud1_id
'||l_fud2_where||l_ledger_where;
SELECT per.sequence FII_EFFECTIVE_NUM,
CASE WHEN per.start_date > :SD_PRIOR
and per.end_date <= :ASOF_DATE
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_XTD,
CASE WHEN per.end_date <= :SD_PRIOR
THEN f.actual_g ELSE to_number(NULL)
END FII_EA_PRIOR_XTD
FROM fii_time_ent_period per,
fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f,
fii_pmv_aggrt_gt gt
WHERE per.ent_period_id = f.time_id
and f.period_type_id = 32
and per.start_date > :SD_PRIOR_PRIOR
and per.end_date <= :ASOF_DATE
and f.parent_company_id = gt.parent_company_id
and f.company_id = gt.company_id
and f.parent_cost_center_id = gt.parent_cc_id
and f.cost_center_id = gt.cc_id
and f.parent_fin_category_id = gt.parent_fin_category_id
and f.fin_category_id = gt.fin_category_id';
SELECT cy_per.name VIEWBY,
to_char(cy_per.end_date,''DD/MM/YYYY'') FII_EA_MONTH_END_DATE,
inline_view.FII_EA_XTD FII_EA_XTD,
inline_view.FII_EA_PRIOR_XTD FII_EA_PRIOR_XTD,
DECODE(FII_EA_XTD,0,'''',DECODE(NVL(FII_EA_XTD,-999999),-999999,'''',
DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
''pFunctionName=FII_EA_'||l_cat_type||'_TREND_DTL&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
''AS_OF_DATE=FII_EA_MONTH_END_DATE&pFunctionName=FII_'||l_cat_type||'_TREND_DTL&VIEW_BY=TIME+FII_TIME_ENT_PERIOD&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''))) FII_EA_XTD_DRILL
FROM
fii_time_ent_period cy_per,
( SELECT inner_inline_view.fii_effective_num FII_EFFECTIVE_NUM,
sum(FII_EA_XTD) FII_EA_XTD,
sum(FII_EA_PRIOR_XTD) FII_EA_PRIOR_XTD
FROM
( '||l_sqlstmt1||'
'||l_union_all||'
'||l_sqlstmt2||'
) inner_inline_view
GROUP BY inner_inline_view.fii_effective_num
) inline_view
WHERE cy_per.start_date <= :ASOF_DATE
and cy_per.start_date >= :SD_PRIOR
and cy_per.sequence = inline_view.fii_effective_num (+)
ORDER BY cy_per.start_date';