[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstmt := 'select NULL VIEWBY,
NULL FII_MEASURE1,
NULL FII_MEASURE10,
NULL VIEWBYID,
NULL FII_MEASURE2,
NULL FII_MEASURE9,
NULL FII_MEASURE5,
NULL FII_MEASURE7,
NULL FII_MEASURE11,
NULL FII_ATTRIBUTE11,
NULL FII_ATTRIBUTE13,
NULL FII_ATTRIBUTE14,'||l_prior_or_budget||'
NULL FII_MEASURE12,
NULL FII_MEASURE14,
NULL FII_MEASURE15
FROM dual where 1= 2';
select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
to_number(NULL) FII_MEASURE1,
f.viewby_id FII_MEASURE10,
f.viewby_id VIEWBYID,
SUM(CY_ACTUAL) FII_MEASURE2,
SUM(CY_ACTUAL) FII_MEASURE9,
SUM(CY_FORECAST) FII_MEASURE5,
SUM(CY_BUDGET) FII_MEASURE7,
SUM(PY_SPER_END) FII_MEASURE11,
SUM(SUM(CY_ACTUAL)) over() FII_ATTRIBUTE11,
SUM(SUM(CY_FORECAST)) over() FII_ATTRIBUTE13,
(SUM(SUM(CY_FORECAST)) over() - SUM(SUM(PY_SPER_END)) over()) /
ABS(NULLIF(SUM(SUM(PY_SPER_END)) over(),0)) * 100 FII_ATTRIBUTE14,'||l_prior_or_budget||'
SUM(to_number(NULL)) FII_MEASURE12,
DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url||''') FII_MEASURE14,
DECODE(:LOB_ID, f.viewby_id, '''', '''||l_url2||''') FII_MEASURE15
FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
(select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) CY_ACTUAL,
SUM(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
then f.forecast_g
else to_number(NULL) end) CY_FORECAST,
SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
then f.budget_g
else to_number(NULL) end) CY_BUDGET,
to_number(NULL) PY_SPER_END,
to_number(NULL) PY_ACTUAL
FROM fii_time_rpt_struct cal
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where cal.report_date = &BIS_CURRENT_ASOF_DATE
and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join||'
and f.gid = 0
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select /*+ leading(cal) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACTUAL,
to_number(NULL) CY_FORECAST,
to_number(NULL) CY_BUDGET,
SUM(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_SPER_END,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_ACTUAL
FROM fii_time_rpt_struct cal
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
and cal.time_id = f.time_id '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_cat_join|| '
and f.gid = 0
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||') f
where '||fii_gl_util_pkg.g_viewby_join||'
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
order by NVL(FII_MEASURE2, -9999999999) desc';
sqlstmt := 'select NULL VIEWBY,
NULL FII_MEASURE1,
NULL FII_MEASURE9,
NULL FII_MEASURE14,
NULL FII_MEASURE2,
NULL FII_MEASURE5,
NULL FII_MEASURE7,
NULL FII_MEASURE11,
NULL FII_ATTRIBUTE14,
NULL FII_ATTRIBUTE13,
NULL FII_ATTRIBUTE12,
'||l_prior_or_budget||'
NULL FII_MEASURE12,
NULL FII_MEASURE13,
NULL FII_MEASURE15
FROM DUAL
WHERE 1=2';
sqlstmt := 'select
cat_tl2.description VIEWBY,
DECODE(f.viewby_id2 , f.viewby_id, '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG, '||fii_gl_util_pkg.g_viewby_value||') FII_MEASURE1,
f.viewby_id FII_MEASURE9,
f.viewby_id2 FII_MEASURE14,
SUM(CY_ACTUAL) FII_MEASURE2,
SUM(CY_FORECAST) FII_MEASURE5,
SUM(CY_BUDGET) FII_MEASURE7,
SUM(PY_SPER_END) FII_MEASURE11,
SUM(SUM(CY_ACTUAL)) over() FII_ATTRIBUTE14,
(SUM(SUM(CY_FORECAST)) over() - SUM(SUM(CY_BUDGET)) over()) /
NULLIF(SUM(SUM(CY_BUDGET)) over(),0) * 100 FII_ATTRIBUTE13,
SUM(SUM(CY_ACTUAL)) over() /
NULLIF(SUM(SUM(CY_FORECAST)) over(),0) * 100 FII_ATTRIBUTE12,
'||l_prior_or_budget||'SUM(to_number(NULL)) FII_MEASURE12,
DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_summary||''' ) FII_MEASURE13,
DECODE( f.viewby_id2 , f.viewby_id, '''' , '''||l_url_trend||''' ) FII_MEASURE15
/* Disable drills on the category and XTD column when parent category is same as child category */
FROM
'||fii_gl_util_pkg.g_viewby_from_clause||',
fnd_flex_values_tl cat_tl2,
(select
'||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
cat_hier.next_level_fin_cat_id VIEWBY_ID2,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) CY_ACTUAL,
SUM(case when bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
then f.forecast_g
else to_number(NULL) end) CY_FORECAST,
SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
then f.budget_g
else to_number(NULL) end) CY_BUDGET,
to_number(NULL) PY_SPER_END,
to_number(NULL) PY_ACTUAL
FROM fii_time_rpt_struct cal,
fii_fin_item_hierarchies cat_hier
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where cal.report_date = &BIS_CURRENT_ASOF_DATE
and f.fin_category_id = cat_hier.child_fin_cat_id
and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
and cat_hier.child_level <= 2 + cat_hier.parent_level
-- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
-- be loaded at summary nodes
and cal.time_id = f.time_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2||'
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||'
union all
select '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
cat_hier.next_level_fin_cat_id VIEWBY_ID2,
to_number(NULL) CY_ACTUAL,
to_number(NULL) CY_FORECAST,
to_number(NULL) CY_BUDGET,
SUM(case when bitand(cal.record_type_id, :ENT_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_SPER_END,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
then f.actual_g
else to_number(NULL) end) PY_ACTUAL
FROM fii_time_rpt_struct cal,
fii_fin_item_hierarchies cat_hier
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where cal.report_date = &BIS_PREVIOUS_ASOF_DATE
and f.fin_category_id = cat_hier.child_fin_cat_id
and ( f.parent_fin_category_id = cat_hier.NEXT_LEVEL_FIN_CAT_ID or (cat_hier.next_level_is_leaf = ''Y''))
and cat_hier.child_level <= 2 + cat_hier.parent_level
-- Modified join to fix bug 3562244. This join will let us pick up budgets and actuals that might
-- be loaded at summary nodes
and cal.time_id = f.time_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join2|| '
and cal.period_type_id = f.period_type_id
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by cat_hier.next_level_fin_cat_id, '||fii_gl_util_pkg.g_viewby_id||') f
where '||fii_gl_util_pkg.g_viewby_join||'
and cat_tl2.flex_value_id = f.viewby_id2
and cat_tl2.language = userenv(''LANG'')
group by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id, f.viewby_id2
order by cat_tl2.description, '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id';
cy_act_exp_select VARCHAR2(100) := NULL;
cy_prior_exp_select VARCHAR2(100) := NULL;
cy_act_exp_select := ' NVL(SUM(CY_ACT_EXP), 0) FII_ATTRIBUTE3,';
NVL(SUM(CY_ACT_CGS), 0) FII_MEASURE3, '||cy_act_exp_select||'
(NVL(SUM(CY_ACT_REV), 0) - '||l_subtractor3||')/
ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100 FII_MEASURE11,
NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||' FII_ATTRIBUTE11,
(NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') /
ABS(NULLIF(SUM(SUM(CY_ACT_REV)) over(),0)) * 100 FII_ATTRIBUTE12,';
cy_prior_exp_select := 'NVL(SUM('||l_label||'), 0) FII_ATTRIBUTE4,';
cy_prior_exp_select := 'NULL FII_ATTRIBUTE4,';
cy_act_exp_select := ' NULL FII_ATTRIBUTE3,';
NULL FII_MEASURE3, '||cy_act_exp_select||'
NULL FII_MEASURE11,
NULL FII_ATTRIBUTE11,
NULL FII_ATTRIBUTE12,';
select NULL VIEWBY,
NULL FII_MEASURE1,
NULL FII_MEASURE6,
'||sqlstmt1||'
NULL FII_CAL1,
NULL FII_CAL2,
'||cy_prior_exp_select||'
NULL FII_MEASURE4,
NULL FII_MEASURE5,
NULL ATTRIBUTE13,
NULL FII_ATTRIBUTE14,
NULL FII_ATTRIBUTE2,
NULL FII_MEASURE13,
NULL FII_MEASURE14
FROM DUAL
WHERE 1=2 ';
select decode(:LOB_ID, f.viewby_id,decode('''||l_dim_flag||''',''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
to_number(NULL) FII_MEASURE1,
f.viewby_id FII_MEASURE6,
'||sqlstmt1||'
to_number(NULL) FII_CAL1,
to_number(NULL) FII_CAL2,
'||cy_prior_exp_select||'
NVL(SUM('||l_prior||'_REV), 0) FII_MEASURE4,
NVL(SUM('||l_prior||'_CGS), 0) FII_MEASURE5,
((NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') /
ABS(NULLIF(SUM(SUM(CY_ACT_REV)) over(),0)) -
(NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||') /
ABS(NULLIF(SUM(SUM('||l_prior||'_REV)) over(),0))) * 100 FII_ATTRIBUTE13,
((NVL(SUM(SUM(CY_ACT_REV)) over(), 0) - '||l_subtractor2||') - (NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||')) /
ABS(NULLIF((NVL(SUM(SUM('||l_prior||'_REV)) over(), 0) - '||l_subtractor||'),0)) * 100 FII_ATTRIBUTE14,
SUM(to_number(NULL)) FII_ATTRIBUTE2,
(case when NVL(abs((NVL(SUM(CY_ACT_REV), 0) - '||l_subtractor3||')/
ABS(NULLIF(SUM(CY_ACT_REV), 0)) * 100), 1000) > 999.9 THEN NULL WHEN NVL(abs((NVL(SUM('||l_prior||'_REV), 0) - '||l_subtractor4||')/
ABS(NULLIF(SUM('||l_prior||'_REV), 0)) * 100), 1000) > 999.9 THEN NULL ELSE 0 END) FII_MEASURE13,
DECODE(NVL(:LOB_ID,-9999), f.viewby_id, '''', '''||l_url||''') FII_MEASURE14
FROM '||fii_gl_util_pkg.g_viewby_from_clause||',
(select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.actual_g
else to_number(NULL) end) CY_ACT_REV,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.actual_g
else to_number(NULL) end) CY_ACT_EXP,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.actual_g
else to_number(NULL) end) CY_ACT_CGS,
SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.budget_g
else to_number(NULL) end) CY_BUD_REV,
SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.budget_g
else to_number(NULL) end) CY_BUD_CGS,
SUM(case when bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.budget_g
else to_number(NULL) end) CY_BUD_EXP,
to_number(NULL) PY_ACT_REV,
to_number(NULL) PY_ACT_EXP,
to_number(NULL) PY_ACT_CGS,
to_number(NULL) PYPER_ACT_REV,
to_number(NULL) PYPER_ACT_CGS
FROM fii_time_rpt_struct cal,
fii_fin_cat_type_assgns assgns
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where assgns.fin_category_id = f.fin_category_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
and cal.time_id = f.time_id
and cal.period_type_id = f.period_type_id
and cal.report_date = &BIS_CURRENT_ASOF_DATE
and bitand(cal.record_type_id, :WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||'
union all
select /*+ leading(cal) index(f FII_GL_MGMT_SUM_MV_N1) */ '||fii_gl_util_pkg.g_viewby_id||' VIEWBY_ID,
to_number(NULL) CY_ACT_REV,
to_number(NULL) CY_ACT_EXP,
to_number(NULL) CY_ACT_CGS,
to_number(NULL) CY_BUD_REV,
to_number(NULL) CY_BUD_CGS,
to_number(NULL) CY_BUD_EXP,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''R''
then f.actual_g
else to_number(NULL) end) PY_ACT_REV,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''OE''
then f.actual_g
else to_number(NULL) end) PY_ACT_EXP,
SUM(case when bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
and assgns.fin_cat_type_code = ''CGS''
then f.actual_g
else to_number(NULL) end) PY_ACT_CGS,
to_number(NULL) PYPER_ACT_REV,
to_number(NULL) PYPER_ACT_CGS
FROM fii_time_rpt_struct cal,
fii_fin_cat_type_assgns assgns
'||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_lob_from_clause||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
where assgns.fin_category_id = f.fin_category_id
'||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_lob_join||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
and cal.time_id = f.time_id
and cal.period_type_id = f.period_type_id
and cal.report_date = &BIS_PREVIOUS_ASOF_DATE
and bitand(cal.record_type_id, :ACT_WHERE_PERIOD_TYPE) = cal.record_type_id
group by '||fii_gl_util_pkg.g_viewby_id||') f
where '||fii_gl_util_pkg.g_viewby_join||'
group by '||fii_gl_util_pkg.g_viewby_value||', f.viewby_id
order by NVL(FII_MEASURE11, -9999999999) desc';